DAX Prior Year To Date Measure

DAX DAX Calculations
1

Hello Datafam,

I have been stuck on a prior year to date formula and would really appreciate some help.

I have a simple mocked up dataset attached. I am trying to find the Total Count of YTD and Total Count of Prior YTD. I have tried many variations of measures with no success.

My measures highlighted on the left are not working. Current YTD should be 10 and Prior YTD should be 7.

File is attached.

image
image1124Ă—467 19.5 KB

Test dax problem.pbix (96.4 KB)

2

Hello @KimC,

Thank You for posting your query onto the Forum.

There’re two reasons why it’s not yielding the results as per the expectations.

1). Date Context Ignored Inside The Multi-Row Card Visual When Working With Time Intelligence Functions -

When results are evaluated in the form of time intelligence functions i.e., in this case by using “DATEADD()” as well as “DATESYTD()” functions, you need to provide date context either inside the visual or in the form of external filter context.

The reason why it’s yielding the results in the form of table visual is because inside the visual itself you’ve provided the context in the form of dates and therefore, it evaluates the results accordingly. But in the case of card visual, there’s nowhere presence of date context and therefore, it’s unable to pass the context of date and hence it gives the results as “BLANKS”.

2). Filter Context Coming From The Filter Pane Inside The Table Visual -

Secondly, inside your table visual, you’re supplying external filter context in the form of Filter Pane by putting the conditions as - “Month Name <> Jul”, “Qtr = Q1, Q2 and Q3” and “Year = 2021 or 2022” but the same cannot be passed onto the Multi-Row Card visual since that will be applicable to entire card.

Based on the above limitations stated, you’ll be required to create the additional measures with the necessary context provided in them, as you’ve provided inside your table visual, in order to put them inside your Multi-Row Card visual. Below are the measures alongwith the screenshot of the final results provided for the reference -

Count YTD - Harsh = 
CALCULATE( [Count Total] ,
    Dates[FiscalYearOffset] = 0 ,
    Dates[Qtr] IN { "Q1" , "Q2" , "Q3" } ,
    Dates[Month Name] <> "Jul" )

Count Prior YTD - Harsh = 
CALCULATE( [Count Total] ,
    Dates[FiscalYearOffset] = -1 ,
    Dates[Qtr] IN { "Q1" , "Q2" , "Q3" } ,
    Dates[Month Name] <> "Jul" )

Final Results
Final Results1349Ă—688 35.6 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Test DAX Problem - Harsh.pbix (97.2 KB)

1 Like
3

Hi @Harsh
Is it possible to do this however without context? My two tables were provided as an example only to show the result I wanted. I don’t want to hard code the measure but want it to be dynamic.

4

Hello @KimC,

If that would’ve been possible then I would’ve done it in the first instance itself. As I’ve stated in the above post, since cards (Both Single Card as well as Multi-Row Card) cannot have a date context inside them like it’s provided inside the table visual, you’ll be required to provide one either by adding a date slicer onto your page or by writing the measure by providing the context in it so that results are evaluated accordingly.

Secondly, in this case, no it’s not possible to do it dynamic because ultimately the way you’ve provided the conditions inside the table visual are in the form of manual intervention. And the same logic has been inserted inside the measure.

And hence, either you’ll be required to put slicer onto the page in order to insert the external date context inside the card or put the logic inside the measure, in order to get the results as required.

Thanks and Warm Regards,
Harsh

5

Hi @KimC

From what I can tell your FY starts in November? This seems to work and I believe should be dynamic, maybe not the most elegant solution!

Count YTD 2 = 
VAR startYear = IF( MONTH( TODAY() ) >= 11, YEAR( TODAY() ), YEAR( TODAY() ) - 1) 
VAR startDate = DATE(startYear,11,1)
VAR endDate = TODAY()
RETURN
CALCULATE(
    [Count Total],
    FILTER( ALL( Dates ),
        Dates[Date] >= startDate &&
        Dates[Date] <= endDate
    )
)

Count Prior Year YTD 2 = 
VAR startYear = IF( MONTH( TODAY() ) >= 11, YEAR( TODAY() ) - 1, YEAR( TODAY() ) - 2)
VAR startDate = DATE(startYear,11,1)
VAR endDate = DATE( YEAR( TODAY() ) - 1, MONTH( TODAY() ), DAY( TODAY() ) )
RETURN
CALCULATE(
    [Count Total],
    FILTER( ALL( Dates ),
        Dates[Date] >= startDate &&
        Dates[Date] <= endDate
    )
)
6

Hi @Harsh & @jamie.bryan,

Thank you both for helping me :raised_hands: :raised_hands:

Yes Jamie, FY starts in Nov and your solution is amazing. Thank you so much!