Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Community Members,
I would like to calculate the date when my measure (Running Total Numeric Distribution) crosses a specific value.
For example I can read from the visual that 30% level of the measure was achieved on 04/07/2012 for “Australia” distributor.
However, I would like to get a result of specific date as a measure to be able to use it on a Card visual.
My data model is very simple. I just use1 fact table plus date table plus KPI table .
My measures:
Total Num of Stores = DISTINCTCOUNT(Distribution[Store])
Total Num of Active Stores = CALCULATE( DISTINCTCOUNT(Distribution[Store]), Filter(Distribution, 'Distribution'[DeliveryDate]>=FIRSTDATE('Date'[Date]) && 'Distribution'[DeliveryDate]<=LASTDATE('Date'[Date])))
Numeric Distribution = DIVIDE([Total Num of Active Stores],[Total Num of Stores],0)
Running Total Numeric Distribution = CALCULATE ( [Numeric Distribution], FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
Can anyone help me?
Pawel
Hi @paweldm,
If I understand you correctly, you should be able to use the formula below to create a new measure to calculate Running Total Numeric Distribution For Specific Date, then show it on a Card visual.
Running Total Numeric Distribution For Specific Date = VAR someDate = DATE ( 2017, 11, 22 ) RETURN CALCULATE ( [Numeric Distribution], FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= someDate ) )
Regards
Thank you for your answer.
However I need find out what the date for the specific value Running Total Numeric Distribution is.
In other words I would like to have a measure to calculate a "Date for Specific value of Running Total Numeric Distribution".
I imagine my variable could be for example 10% value of Running Total Numeric Distribution and measure should calculate a first date when that value has been achived.
Pawel
@paweldm can you share sample raw data in excel, will help to provide the solution. I recently did something similar but different use case.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Hi @parry2k
You can load the data from the Google drive https://drive.google.com/file/d/1RCtcyzTQC8FBBmKbhqWK_OQZnPy-WYXS/view?usp=sharing
BR
Pawel
@paweldm didn't see all the measure you mentioned in this thread? I assumed those are in place, can you send the revised pbix.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Hi @parry2k
You can load the data from the Google drive https://drive.google.com/file/d/1RCtcyzTQC8FBBmKbhqWK_OQZnPy-WYXS/view?usp=sharing
BR
Pawel
HI again @v-ljerr-msft
I have modified your measure and I think I am a bit closer to the solution. I added parameters so now the report is more dynamic, though it is not the optimal solution since I don't have a single measure to identify specific date and it is still manual with parameters setup to identify the first date. Anyway once again thank you very much for your support.
Specific Date = DATE ( 'Parameter Year '[Year Value], 'Parameter Month'[Month Value], 'Parameter Day'[Day Value] )
Running Total Numeric Distribution For Specific Date = VAR someDate = DATE ( 'Parameter Year '[Year Value], 'Parameter Month'[Month Value], 'Parameter Day'[Day Value] ) RETURN CALCULATE ( [Numeric Distribution], FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= someDate ) )
BR
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |