# DAX Exercise for fun š

DAX Problem 1.pbix (132.7 KB)

This is what you need to accomplish (Challenge Measure):

Caveat - You canāt use IF/SWITCH function.

4 Likes

2 Likes

Got the main measure, but are we not allowed to use IF/SWITCH even for fixing matrix totals?

• Brian

Nice one
Pro tip, mark your date table as a date table LOL

``````Challenge =
CALCULATE(
CALCULATE( [Total Sales],
FILTER(
ALL( Dates[Date] ),
Dates[Date] <= MAX( Dates[Date] )
)
),  DATEADD( Dates[Date], -2, MONTH )
)
``````
4 Likes

@BrianJ Nope, thatās the fun part.

1 Like

@Melissa Awesome!

Did you have to think for a while about where to evaluate MAX? I was working on this late at night and I had MAX outside of CALCULATE and had to think for some time why it wasnāt working!

1 Like

Thanks @AntrikshSharma

No because it has to be in context of the visualā¦
I spend more time examining the date table than writing the measure, to be honestā¦

2 Likes

Ah

1 Like

For the win! (explanation hidden under summary below)

Summary

My initial strategy focused on development of a DAX-based index that would cross years, that I could back up two periods in the cumulative calculation. This worked like a champ, except that Power BI threw up a big

for the totals and substotals. Here was the Index measure I developed:

and then the Challenge measure that used that Index:

When the totals fell over, I realized that the index was just effectively backing up the dates by 2 months, so I tried DATEADD to do the same thing, figuring that perhaps the transition to the index and back again was screwing up the totals. If Iād been allowed to use IF(HASONEVALUE) or IF(HASONEFILTER), I could have fixed the first Challenge measure, but the Grinch stole those along with my Date Offsetsā¦

So, hereās the Challenge 2 measure using DATEADD instead of the DAX index.

I canāt say I understand exactly why this doesnāt screw up the totals also, but it doesnāt so Iāll take that as a win.

Very cool problem. Thanks for posting this - really enjoyed working on it!

• Brian
4 Likes

Thanks for your new initiative itās really fun to learn with this. Here is my solution

Summary
``````Challenge =
VAR TimeTravel =
CALCULATE ( MAX (  Dates[Date] ), PARALLELPERIOD(  Dates[Date], -2, MONTH ) )
VAR Result =
CALCULATE (
[Total Sales],
FILTER ( ALLSELECTED ( Dates ),  Dates[Date] <= TimeTravel )
)
RETURN
Result
``````
Summary

Run the filter context first to get the Running total and then shifting that with 2 months.

Thanks
Mukesh

3 Likes

Wonderfully concise solution - nicely done!

• Brian
1 Like

Anymore takers?

May be it didnāt spread to wider audience @BrianJ and @Melissa has ability to spread it wider/faster .

Thanks
Mukesh

F Pastor =
CALCULATE(
CALCULATE (
[Total Sales],
FILTER (
ALL ( Dates[Date] ),
āDatesā[Date] <= MAX ( Dates[Date] )
)
),
)

3 Likes

@FPastor You might want to hide the DAX you posted by using the spoiler effect.

If you highlight your DAX and then go to the cog in the message bar and use the spoiler button/effect

Thanks David, good to know for the next oneā¦

1 Like

cog, is this a new DAX measure where do i find itā¦

Donāt worry Federico cog is part of the message bar when you are typing a message on the forum. I wasnāt very clear.

Thank David - youāre the best

2 Likes

Here are some variations I could come up with, mostly logic is same but you can see how much you can modify a simple code: DAX Problem 1.pbix (143.2 KB)

``````M1 =
CALCULATE (
VAR MaxDate = MAX ( Dates[Date] )
RETURN
CALCULATE (
[Total Sales],
Dates[Date] <= MaxDate,
REMOVEFILTERS ( Dates )
),
DATEADD ( Dates[Date], -2, MONTH )
)
``````

.

``````M2 =
CALCULATE (
VAR MaxDate =
MAX ( Dates[Date] )
VAR DatesAndSales =
ADDCOLUMNS ( ALL ( Dates[Date] ), "@Sales", [Total Sales] )
VAR Result =
FILTER ( DatesAndSales, Dates[Date] <= MaxDate )
RETURN
SUMX ( Result, [@Sales] ),
DATEADD ( Dates[Date], -2, MONTH )
)
``````

.

``````M3 =
VAR StartDate =
CALCULATE ( MAX ( Dates[Date] ), DATEADD ( Dates[Date], -2, MONTH ) )
VAR Result =
CALCULATETABLE (
VALUES ( Dates[Date] ),
"@Sales", [Total Sales]
),
REMOVEFILTERS ( Dates )
)
RETURN
SUMX (
FILTER ( Result, Dates[Date] <= StartDate ),
[@Sales]
)
``````

.

``````M4 =
VAR StartDate =
CALCULATE ( MAX ( Dates[Date] ), DATEADD ( Dates[Date], -2, MONTH ) )
VAR Result =
CALCULATE (
[Total Sales],
CALCULATETABLE ( ALL ( Dates ) ),
Dates[Date] <= StartDate
)
RETURN
Result
``````

.

``````M5 =
VAR StartDate =
CALCULATE ( MAX ( Dates[Date] ), DATEADD ( Dates[Date], -2, MONTH ) )
VAR DatesTemp =
CALCULATETABLE (
VALUES ( Dates[Date] ),
REMOVEFILTERS ( Dates )
)
RETURN
CALCULATE (
[Total Sales],
DatesTemp,
Dates[Date] <= StartDate
)``````
2 Likes