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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.