cancel
Showing results for
Did you mean:

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

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?

4 REPLIES 4
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.

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.

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:

 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

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors