Numeric Distribution Calculation (thread 2)

[paweldm]

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

[v-ljerr-msft]

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

[paweldm]

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

[parry2k]

@paweldm can you share sample raw data in excel, will help to provide the solution. I recently did something similar but different use case.

[paweldm]

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

[parry2k]

@paweldm didn't see all the measure you mentioned in this thread? I assumed those are in place, can you send the revised pbix.

[paweldm]

@parry2k please load it from :

Updated PBIX file

[paweldm]

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]

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