DAX - measures issue for Selectedvalue(daterank) +1

[szczawek]

Hello,

i am trying to build up a measure for a line chart that will show absolute change of period vs previous period. Due to calendar details i can't use time intelligence functions.

The issue is that when i am setting variable for currnet period (select date rank for selected datapoint and calculate result for it) everything is working fine, but when i want to create measure for Previous Period result i do not ge any value - the only change is that date rank is current period data rank +1.

Details below - could let me know what i am doing wrong or propose any solution?

[v-yalanwu-msft]

Hi， @szczawek ；

Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @szczawek ;

I'm not quite clear about your table structure and the results you want to output, can you share more details or a simple example and scenario? And the result display that you want to output.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[szczawek]

Hi  @amitchandak ,

Thank for your input, but in this case it is not that simple.

I have values for random number of days in Period, but always for the first.

Also this is status for given moment so i need precisely value from specifict date pointed by DateRank.

The rank of dates is covered in Power Query, so i don't need to calculate it in DAX.

Logic for DataRank is 1 for maximum avaialble date in Fact table and then n+1 for each first day of a previous periods including current. So examples given before could be extended to rank dates as follow:

DateRank	Date
1	2022-05-07
1750	2022-05-03
2	2022-05-01
1749	2022-04-25
1748	2022-04-11
3	2022-04-01
4	2022-03-01

Now when i am calculating PeriodPrevTag the result is ok - if rank is 1 then PeriodPrevTag return 2, if it is 5 then it returns 6 etc. 

The issue that i am facing is that when i try to calculate result for rank 1 with PeriodPrevTag (so in fact i need numbers for DateRank = 2 as result).

I have working solution on this topic but i wanted to make it much simplier and I am not sure why measure from first post is not working.

I am trying to understand it to have better knowledge for the futter when simmilar cases occure 😉

Kind regards

[amitchandak]

@szczawek , for all custom period, we try to create a rank using YYYYPP or period start date and use that

Column //Prefer a date table 

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

measures
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

PTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))

refer

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s