DAX Exercise for fun 😁

AntrikshSharma · June 22, 2021, 6:55pm

Here is your data:

DAX Problem 1.pbix (132.7 KB)

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

Caveat - You can’t use IF/SWITCH function.

BrianJ · June 22, 2021, 8:40pm

BrianJ · June 23, 2021, 10:28am

@AntrikshSharma ,

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

Brian

Melissa · June 23, 2021, 10:58am

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 )
)

AntrikshSharma · June 23, 2021, 11:24am

@BrianJ Nope, that’s the fun part.

AntrikshSharma · June 23, 2021, 11:26am

@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!

Melissa · June 23, 2021, 11:32am

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…

DavieJoe · June 23, 2021, 3:00pm

Ah

BrianJ · June 23, 2021, 4:16pm

@AntrikshSharma ,

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

MK3010 · June 25, 2021, 6:46am

Hi @AntrikshSharma

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

BrianJ · June 25, 2021, 6:57am

@MK3010 ,

Wonderfully concise solution - nicely done!

Brian

AntrikshSharma · June 26, 2021, 5:14am

Anymore takers?

MK3010 · June 26, 2021, 6:55am

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

Thanks
Mukesh

FPastor · June 28, 2021, 2:18pm

F Pastor =
CALCULATE(
CALCULATE (
[Total Sales],
FILTER (
ALL ( Dates[Date] ),
‘Dates’[Date] <= MAX ( Dates[Date] )
)
),
DATEADD( Dates[Date], -2, MONTH)
)

DavieJoe · June 28, 2021, 2:21pm

@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

FPastor · June 28, 2021, 2:36pm

Thanks David, good to know for the next one…

FPastor · June 28, 2021, 2:39pm

cog, is this a new DAX measure where do i find it…

DavieJoe · June 28, 2021, 2:53pm

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.

FPastor · June 28, 2021, 3:06pm

Thank David - you’re the best

AntrikshSharma · June 29, 2021, 4:40pm

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 ( 
        ADDCOLUMNS ( 
            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
    )