Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paweldm
Helper II
Helper II

Numeric Distribution Calculation (thread 2)

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.

 

PrtScr_1.jpg

 

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 . 

 

PrtScr_2.jpg

 

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

8 REPLIES 8
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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

HI @v-ljerr-msft

 

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.

@parry2k please load it from :

 

Updated PBIX file

 

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. 

PrtScr_3a.jpg

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.