Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
szczawek
Frequent Visitor

DAX - measures issue for Selectedvalue(daterank) +1

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?

 

szczawek_0-1651791061006.png

 

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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
Community Support
Community Support

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
Frequent Visitor

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:

DateRankDate
12022-05-07
17502022-05-03
22022-05-01
17492022-04-25
17482022-04-11
32022-04-01
42022-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
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.