IF statement regarding date

[KCantorParticipant Started: 13Replies: 30Total: 43]

I know I am missing something simple here but I am stumped. I have a table called SalesPerformance with a column called [OrderDate]. I need to create two flags in a calculated column. I need a flag showing if the order date occurred in the past 12 months (not calendar year) and another flag that show whether the order date occurred in the previous 12 months. It could be in the same column I just need a way to flag orders for the past twelve months from today and flag order for the 12 months before that while ignoring those before that.

the end result is a flag to be used with the customer table to calculate customers who purchased in the past 12 months that also purchased in the previous 12 months. Our customer table is crap so I am hoping to use the good data in the sales table to flag transactions and then use a distinct count of customer numbers. I may be out in left field with this.

any help is appreciated.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Kristi,

So you are trying to get a count of customers who purchased at least once in the last 12 months and at least once within the prior 12 months?

If so, I think it would be important to establish what “today” is: if you use the TODAY() function, it might not get the right results (because TODAY() is like a moving target, while your order dates are not). To this end, you could start with a measure for a data freshness date, such as

Data Date = CALCULATE ( MAX ( SalesPerformance[OrderDate] ), ALL ( SalesPerformance ) )

The next thing that I think is important: What is meant by “months from today”: could you use dates between 0 and 364 days from [Data Date] for the past 12 months, and dates between 365 and 629 from the [Data Date] for the prior two months?

Before continuing, why is your Customer table crap? Is it missing customers with transactions in the SalesPerformance table or is the Customer table bloated with customers who have not purchased anything in a long, long time (or maybe even contain prospective customers who have never bought anything yet)?

I recently worked with a case a lot like your request, so I hopefully will be able to help.

Looking forward to your answers.

Tom

[KCantorParticipant Started: 13Replies: 30Total: 43]

Tom,

It is always a pleasure to work with you.

I can use the freshness date, I have that calculation already in the report to show the last refresh as well. I could also use the dates between 0-364 and 365-629.

You hit the nail on the head with the customer table. There are customers who bought many years ago. so many years, in fact, that our fact table doesn’t contain the data. There are potential customers who have never purchased. There are also failed attempts at entering customer information in resulting in customers named “.”, “\”, or “10-inch fry Pan”. And duplicates . . .  oh the duplicates! We have multiple entries per company per rep and multiple heinous errors such as differentiating McDonalds from mcdonalds from MacDonalds due to data entry processes that have been/are being addressed.

I clean as much up as I can in Power Query but the bloat really kills performance. If the customer calculations can be built in the fact table, it will perform much better. The customer table (and the UPS file) are the bane of my existence and neither can really be repaired at the source at this time. Any guidance you could offer would be greatly appreciated.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Does your model have a calendar (dates) table linked to the order date of the sales performance table?

[KCantorParticipant Started: 13Replies: 30Total: 43]

Here is a snip of my data model.

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

I may have a better solution than the following, but it depends on the results that you get for this solution (I have assumed the [Sell to Customer] column is the customer key linking SalesPerformance with Customer):

First, create a calculated column in the SalesPerformance table, that will divide any sale into 1 of three categories (“Lst 12 Mo”, “Prv 12 Mo”, and “Other”):

Period Ordered =
IF (
    DATEDIFF ( SalesPerformance[OrderDate], [Freshness Date], DAY ) <= 364,
    "Lst 12 Mo",
    IF (
        DATEDIFF ( SalesPerformance[OrderDate], [Freshness Date], DAY ) <= 629,
        "Prv 12 Mo",
        "Other"
    )
)

Now create the following measure:

Customers Active - Last 12 and Prev 12 Months :=
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER (
                SalesPerformance,
                OR ( SalesPerformance[Period Ordered] = "Lst 12 Mo", SalesPerformance[Period Ordered] = "Prv 12 Mo" )
            ),
            SalesPerformance[Sell To Customer],
            "Active Periods", DISTINCTCOUNT ( SalesPerformance[Period Ordered] )
        ),
        [Active Periods] = 2
    )
)

If you are looking for a single result (neither sliced, nor filtered in a pivot), the above measure should work.

For a possible better solution, please advise the total unfiltered number returned for the measure in the Power Pivot window.

[KCantorParticipant Started: 13Replies: 30Total: 43]

Tom,

I seem to be returning a result of Blank. My freshness date shows as 09/27/2017 but when I visit the sales performance table every row shows “Lst 12 Mo” with none showing as “Prv 12 Mo”. Even the lines with order dates of Jan 1, 2013.

[KCantorParticipant Started: 13Replies: 30Total: 43]

My bad, The freshness date was a measure and was iterating for each row of the table. I changed the way it was calculated and now the measures are performing as they should. I currently whos Customers Active — Last 12 and Prev 12 as being equal to 14,707 with no filtering or slicers.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

OK. Here is my “other” solution:

We will add a calculated column to your customers table that will identify those that have purchased sometime within the last 12 months AND in the previous 12 months. Of course you could call the calculated column whatever you like, but for discussion here, I will call it “Act-Lst 12 and Prv 12”. Definitely too wordy, but for discussion’s sake, it will do.

So the formula for the new calculated column in the Customer table will look something like this:

=
IF (
    CALCULATE (
        DISTINCTCOUNT ( SalesPerformance[Period Ordered] ),
        FILTER (
            RELATEDTABLE ( SalesPerformance ),
            OR (
                SalesPerformance[Period Ordered] = "Lst 12 Mo",
                SalesPerformance[Period Ordered] = "Prv 12 Mo"
            )
        )
    )
        = 2,
    1,
    0
)

Then, the measure for “Customers Active – Last 12 and Prev 12 Months” will look something like this (and it will play well in any pivot with any amount of slicers or filters, as long as you have customers on rows or columns of the pivot).

Customers Active - Last 12 and Prev 12 Months Option 2:=
COUNTROWS ( FILTER ( Customer, Customer[Act-Lst 12 and Prv 12] = 1 ) )

Or, since a 1 is a “yes” and a 0 is “no”, it could be written like this:

Customers Active - Last 12 and Prev 12 Months Option 3:=
SUMX ( Customer, Customer[Act-Lst 12 and Prv 12] )

All three ways of writing the measure (assuming my spelling is correct and consistent) should return the same results.

Let me know if this works as written, and we will go from there. As far as performance goes, the Power Pivot formula engine should be able to work quickly with the new calculated column because it will only ever have either a 1 or a 0 for each row.

Tom

[KCantorParticipant Started: 13Replies: 30Total: 43]

Tom,

I have attempted this last calculation Unfortunately, my PowerBI just churns and never really finishes the if statement. It will either max out my CPU, or my Disk from the Microsoft SQL Server Analysis Services process. I have had to resort to using End Task to get my computer to respond after that.

Current rendition shows 793,633 rows on the customer table.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Kristi,

Sorry to hear. At least you have the original formula that did not produce grief.

Tom

[KCantorParticipant Started: 13Replies: 30Total: 43]

Thank you for your help Tom. The working calculation has appeased the Powers that be. I am working on cleaning up both the fact table and the look up table with the hope of revisiting your last suggestion. I will update you with that progress soon.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

I had a chance to discuss option with another DAX person who suggested the DISTINCTCOUNT and the SUMX, combined with the size of your customer table, could have produced the grief you experienced. Here is another idea to consider:

In your Customer table, three calculated columns:

Act in Lst 12 Mo = COUNTROWS ( FILTER ( RELATEDTABLE ( SalesPerformance ), [Period Ordered] = “Lst 12 Mo” ) )

Act in Prv 12 Mo = COUNTROWS ( FILTER ( RELATEDTABLE ( SalesPerformance ), [Period Ordered] = “Prv 12 Mo” ) )

Act in Both Periods = IF ( [Act in Lst 12 Mo] > 0 && [Act in Lst 12 Mo] > 0, 1, 0 )

Then the measure would be

Customers Active – Last 12 and Prev 12 Months = SUM ( Customer[Act in Both Periods] )

Let me know if the above works for you. If it does, then we could look at combining the three calculated columns into 1.

Tom

[KCantorParticipant Started: 13Replies: 30Total: 43]

 

Tom,

The three columns and the measure are working appropriately.  It is returning slightly different numbers than the original version we tried that worked.  The old one was 14,714 and the new one is 14,798.

I am very interested in your combined version as I am finding this very educational.

One other question. Have you read the book “If you give a mouse a cookie”? I only ask because now my team would like to see how this changes if we step back in time on a monthly basis. So where our original calculation was 0-12 or 12-24,  what about 13-25, 14-26 . . .

I am almost positive that Rob warned me this would happen.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Stuff like this happens when people get a step closer to the truth than they were before (somebody is helping them find a light switch).

Why there is a variance: usually when a variance occurs there is some issue with a filter, overlapping periods or dirty data (for example, sales orders that total to 0 in currency, but still have rows of data). Maybe a filter that checks for amount instead of rows would be more insightful.

If the following works for you, you can delete the other three calculated columns, rename this one to “Act in Both Periods” so it will work with your measure.

Act in Both Periods in a Sngl Col =
IF (
    COUNTROWS (
        FILTER ( RELATEDTABLE ( SalesPerformance ), [Period Ordered] = "Lst 12 Mo" )
    )
        > 0
        && COUNTROWS (
            FILTER ( RELATEDTABLE ( SalesPerformance ), [Period Ordered] = "Prv 12 Mo" )
        )
            > 0,
    1,
    0
)

When users start to play with the data, it is time to consider a measure instead of calculated columns.

Please let me know if the new formula works for you.

Tom

[Author]

Posts