DAX formula help required

Anything related to PowerPivot and DAX Formuale
MendipMunger
Posts: 5
Joined: Sun Oct 08, 2017 12:19 am

DAX formula help required

I've been struggling to write a DAX measure today and even got a bit miserable about it (poor me ). I'm sitting down with the 'Learn to Write DAX' book now but not having much luck finding what I'm looking for, nor with a Google search.

I have a list of transactions for the last 100 days: each day a steadily growing list of customers has one transaction amount per day. Customers can be categorised in two ways. For one type of customer I want a rolling last 30 day sum of their transactions and for the other type I want a last 60 day sum of their transactions.

I know how to write a sumx-if measure to calculate a sum depending on the type of customer and I can write a measure to do a last 30 day sum. But I can't work out how to do both in one measure.

Would anyone be able to help please? I would like to use the measure to return a table or pivot table showing each customer and their last x day sum of transactions.

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: DAX formula help required

My book is designed as a teaching aid. If you read it from front to back and do the exercises, you will know how to solve this problem. For now, read chapter 14, the section titled “custom time intelligence”. If you follow the pattern you should be fine, even if you don’t understand it until you complete the book.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

MendipMunger
Posts: 5
Joined: Sun Oct 08, 2017 12:19 am

Re: DAX formula help required

Thanks for the advice to read your book thoroughly !! It worked to the point where I can construct the DAX for filtering on ONE table (the Date table).

However, the real reason I am struggling is because I don't know how to filter on TWO different tables at the same time and also to use an OR operator between the two sets of filtering.

Here is what I came up with - do you think it works ? I don't have a PC this weekend to test it on so am trying to solve it on a tablet without Excel.

I used the double pipe || to create an OR operator in the filtering that CALCULATE uses to give this sum:

filter date table using 60 days AND filter sales table for customer type X

OR

filter date table using 30 days AND filter sales table for customer type Y

[Moving Total] :=
IF (
DISTINCTCOUNT ( Dates[Date] ) < 60,
0,
CALCULATE (
[Total Amount],
FILTER (
ALL ( Dates ),
Dates[Date]
> MAX ( Dates[Date] ) - 60
&& Dates[Date] <= LASTDATE ( Dates[Date] )
),
FILTER ( ALL ( Sales ), Sales[Customer Type] = "X" )
|| FILTER (
ALL ( Dates ),
Dates[Date]
> MAX ( Dates[Date] ) - 30
&& Dates[Date] <= LASTDATE ( Dates[Date] )
),
FILTER ( ALL ( Sales ), Sales[Customer Type] = "Y" )
)
)

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: DAX formula help required

I wouldn’t do it this way. How about write a measure that gives the correct 60 day total. Then write another measure that gives the correct 30 day total. Finally write a measure that switches between the other 2 measures depending on the type of customer. You can use variables and do this within one measure if you like. Just write each part, one at a time to test it first.

Interested in leaning DAX? See my Dax Book.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

MendipMunger
Posts: 5
Joined: Sun Oct 08, 2017 12:19 am

Re: DAX formula help required

Doh !! Why didn't I think of that ! The best solutions are often the simplest and most "obvious". I reckon I can do that. Thanks for your help Matt - this has convinced me to actually read your book through AND complete the exercises.

MendipMunger
Posts: 5
Joined: Sun Oct 08, 2017 12:19 am

Re: DAX formula help required

Here's what I used in the end (at the end of this post). I did break it down but then I put it back together again just because I thought it would work OK.

It does now work OK as I followed the advice in the book to use a pivot table to test the output. However, a few problems arose :

When I split out the elements and wrote a measure to choose the separate measure based on type, I got the correct output for one customer type but not the other. When I joined them in to one measure like I have below, I get the correct result.

When I used the FILTER clause to filter for dates in the date table, my measures just returned <blank>. But when I used the DATESINPERIOD function (from a 2013 Rob Collie post) I just referenced the transaction dates in my data table and it worked! So I realised that TimeIntelligence works without a date table.

I also added the third CALCULATE for customer types not equal to G or S, as I wanted them returned in my output and I didn't have a separate customer table. I also adapted this to create another measure that gives the previous X-day period, so I can compare the difference.

I appreciate its a bit hacky and not that amazing but I'm pleased that I got a problem solved that I initially thought would be a piece of cake (this replaces several worksheets of sum-ifs). I'm also very grateful for your guidance in helping me to solve the problem.

Current :=
CALCULATE (
[Total],
DATESINPERIOD (
Extract[Settlement Date],
LASTDATE (Extract[Settlement Date] ),
- [GDays],
DAY
),
FILTER (Extract, Extract[Type] = "G" )
)
+ CALCULATE (
[Total],
DATESINPERIOD (
Extract[Settlement Date],
LASTDATE (Extract[Settlement Date] ),
- [SDays],
DAY
),
FILTER (Extract, Extract[Type] = "S" )
)
+ CALCULATE (
[Total] * 0,
FILTER (
Extract,
Extract[Type] <> "G"
&& Extract[Type] <> "S"
)
)

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: DAX formula help required

Sorry, I’m not clear. Did you write 2 different measures, one for cust type G and one for cust type S? When you put these 2 measures into a table with individual customers, do the 2 measures give the correct answer for the correct customer type? All that matters is that the correct measure gives the correct answer for the relevant type of customer. It doesn’t matter if the wrong measure gives the wrong answer for the incorrect customer type.

Then write a switch

=SWITCH(firstnonblank(customer[type],1),
“G”,[Measure 1],
“S”,[Measure 2]
)

This will only work at the customer level. If you need the total to add up, you will need to wrap he entire thing inside a SUMX. This may or may not be performant. If not, you may have to re-look at the approach.