cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Days Between Slicer Dates in Measure

Hi, 

 

I am trying to create a measure the calculates a straigt line average across my slicer range. I am having trouble setting the 'Remaining Days' (from my slicer delta and my last date of sales) for all data points. 

 

Slicer Range.PNG

 

As you can see, when I use my 'Calendar'[Date] (which is my related calendar table) with the [Remaining Days] Measure, it shows -19 for every date point but sums up to the correct value of 345 in this instance which is the delta between 1/1/2019 and 12/31/2019 and my last sales point of 1/19/2019

1 ACCEPTED SOLUTION
v-cherch-msft
Microsoft
Microsoft

Hi @Anonymous

 

You may try below measure:

Remaining Days =
VAR a =
    FIRSTDATE ( 'Calendar'[Date] )
VAR b =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    DATEDIFF ( a, b, DAY )

1.png

Regards,

Cherie

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

View solution in original post

2 REPLIES 2
v-cherch-msft
Microsoft
Microsoft

Hi @Anonymous

 

You may try below measure:

Remaining Days =
VAR a =
    FIRSTDATE ( 'Calendar'[Date] )
VAR b =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    DATEDIFF ( a, b, DAY )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
parry2k
Super User
Super User

@Anonymous try something like this, add new column, assuming you have "YEAR" colum in your calendar dimension

 

Remaining Days = 
VAR y = CALCULATE( MAX( 'Calendar'[Year] ) )
VAR l = CALCULATE( MAX( 'Calendar'[Date] ), ALL( 'Calendar' ), 'Calendar'[Year] = y )
RETURN 
    DATEDIFF( 'Calendar'[Date], l, DAY ) 





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.