Rough countback for GRNI value vs goods booked in

[Nafeesa]

I want to know roughly how many days of goods receipts the current GRNI balance is made up of;

In the attached example, the GRNI balance was £406k on the 7th of September.

Doing a reverse cumulative total of the total goods booked in from today backwards shows that the balance is made up of roughly 26 days of receipts.

I want to have a variable comparing the current GRNI total balance to the reverse cumulative total and return the date it exceeds the balance. I then want to return the number of days from that date until todays to give a rough countback calculation.

What is the best way to approach this?

 

Thanks,

Nafeesa.

[DataInsights · Accepted solution]

@Nafeesa,

Try this measure:

Days of goods receipts = 
VAR vMaxDate =
    CALCULATE ( MAX ( Goods[Posting Date] ), ALLSELECTED () )
VAR vGRNI = [GRNI]
VAR vTableReverseTotal =
    ADDCOLUMNS (
        VALUES ( Goods[Posting Date] ),
        "@ReverseTotal", [Reverse Cumulative Total of goods booked in]
    )
VAR vTableFlag =
    ADDCOLUMNS ( vTableReverseTotal, "@Flag", IF ( [@ReverseTotal] > vGRNI, 1 ) )
VAR vTableFlagFilter =
    FILTER ( vTableFlag, [@Flag] = 1 )
VAR vDateExceeded =
    MAXX ( vTableFlagFilter, Goods[Posting Date] )
VAR vResult =
    DATEDIFF ( vDateExceeded, vMaxDate, DAY )
RETURN
    vResult