Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
Hi @Anonymous ,
Here is my sample data.
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.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have a time slicer that uses a measure. The measure considers a recordset a part of a time period based on the following formula.
Hi,
Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.
Hi @Anonymous ,
Here is my sample data.
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.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @ras_ile, your solution looks great when selecting granular dates in a slicer.
I tried to make this work while using a date hierarchy selector (e.g. first quarter) and the results are not reliable.
Would you have any suggestions for this?
User | Count |
---|---|
111 | |
101 | |
100 | |
67 | |
35 |
User | Count |
---|---|
143 | |
140 | |
115 | |
97 | |
52 |