cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ras_ile
Helper III
Helper III

DAX measures based on MAX and MIN dates selected in the Time Slicer?

Hi all - 

I am working on two measures that I want to be dynamic as I use the time slicer.

I attach one picture of my fact table (Time key - Account key - Value (monthly) - Value (YTD) - Entity key - Account type).

To give you an idea of how my dataset is built, I have the measures for the actual data values.... Periodic data = Sum([Periodic - Periodic]). and similar for YTD data.

Then, let's play with the idea that I want to have the following measure dynamic with the date slicer:
Total Sales = Calculate([Periodic Data]), ACCOUNT = ["SALVSAEB"]

Now, my question is: How would I re-write the measure above in order to get the data for the MAX (and MIN) time period in my time slicer? E.g. if the Time slicer is showing 2019-07-15, I want it to calculate Total Sales for July, since I have monthly data only.

My guess of the issue: I have only monthly data, as you can see in my fact table and calendar dimension table.
I do not know if the time slicer is compatible with this type of data, but I would also be okay with a solution where I could e.g. check box two different months and have my MIN measure and MAX measure do the rest based on the selected dates. The desired outcome is to eventually be able to select two different time periods and have a KPI card that calculates the difference between MAX and MIN date selected (e.g. Sales +12,5 %)

Any advice on how to write such  DAX measures? I would appreciate any helpful comments. 

Regards,
Rasmus 



Example - fact table.jpgExample - calendar dimension table.jpg

 

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @ras_ile ,

 

Here is my sample data.

1-1.PNG

Then create a calendar table and add a column to save year and month.

YM =
YEAR ( 'Calendar'[Date] ) * 100
    + MONTH ( 'Calendar'[Date] )

Create three measures to display the maximum, minimum and difference.

MAX =
VAR maxd =
    CALCULATE ( MAX ( 'Calendar'[YM] ), ALLSELECTED ( 'Calendar'[YM] ) )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[count] ), 'Table 2'[YM] = maxd )
MIN =
VAR mind =
    CALCULATE ( MIN ( 'Calendar'[YM] ), ALLSELECTED ( 'Calendar'[YM] ) )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[count] ), 'Table 2'[YM] = mind )
Diff =
VAR d = [MAX] - [MIN]
RETURN
    d / [MIN]

Now you can use slicer and card visuals.1-2.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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

View solution in original post

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

Hi @ras_ile ,

 

Here is my sample data.

1-1.PNG

Then create a calendar table and add a column to save year and month.

YM =
YEAR ( 'Calendar'[Date] ) * 100
    + MONTH ( 'Calendar'[Date] )

Create three measures to display the maximum, minimum and difference.

MAX =
VAR maxd =
    CALCULATE ( MAX ( 'Calendar'[YM] ), ALLSELECTED ( 'Calendar'[YM] ) )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[count] ), 'Table 2'[YM] = maxd )
MIN =
VAR mind =
    CALCULATE ( MIN ( 'Calendar'[YM] ), ALLSELECTED ( 'Calendar'[YM] ) )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table 2'[count] ), 'Table 2'[YM] = mind )
Diff =
VAR d = [MAX] - [MIN]
RETURN
    d / [MIN]

Now you can use slicer and card visuals.1-2.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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

View solution in original post

Anonymous
Not applicable

Hi,
Create 2 measures as below to find the minimum and maximum date from selected range.
MinDate = FORMAT(CALCULATE ( MIN(Dates[Date]), ALLSELECTED(Dates) ), "DD-MM-YYYY")
MaxDate = FORMAT(CALCULATE ( MAX(Dates[Date]), ALLSELECTED(Dates) ), "DD-MM-YYYY")
 
result.PNG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors