topic Re: Use previous month value if current month value is blank in DAX Commands and Tips

Use previous month value if current month value is blank

Anonymous — Sun, 04 Sep 2022 18:26:19 GMT

Hi,
I tried to look at other simulare questions but cant make it work.

I need to use last month value if current month value is blank. (sep P-stock)

This is the measure I use for P-Stock

Orderstock - Poseidon Count - Test 2 = VAR ReturnValue = CALCULATE( COUNT( FactPoseidonOrderStock[Location_KEY] ), FILTER( FactPoseidonOrderStock , FactPoseidonOrderStock[StagingDateTime] = MAX( FactPoseidonOrderStock[StagingDateTime] ) ) ) RETURN ReturnValue

This is the data

Jan	533	995	1528
Feb	556	430	986
Mar	624	1239	1863
Apr	639	1305	1944
May	722	1342	2064
Jun	915	1199	2114
Jul	901	1168	2069
Aug	889	1149	2038
Sep	887		887

This is the visuals

Re: Use previous month value if current month value is blank

colacan — Sun, 04 Sep 2022 19:23:10 GMT

Anonymous Hi, I think you can try LASTNONBLANK funcion

VAR ReturnValue = CALCULATE( COUNT( FactPoseidonOrderStock[Location_KEY] ), LASTNONBANK(FactPoseidonOrderStock[StagingDateTime], COUNT( FactPoseidonOrderStock[Location_KEY]) ) )

I hope this helps.

Re: Use previous month value if current month value is blank

Greg_Deckler — Sun, 04 Sep 2022 20:19:20 GMT

Anonymous This would be far simpler if you added an Index to your query or had a real date. For example, if you added an Index you could do this:

P-Stock Measure = VAR __Previous = MAX([Index])-1 VAR __PStock = MAX('Table'[P-Stock]) RETURN IF( ISBLANK(__PStock, MAXX(FILTER(ALL('Table'),[Index] = __Previous),[P-Stock]) __PStock )

Re: Use previous month value if current month value is blank

Anonymous — Mon, 05 Sep 2022 13:45:36 GMT

I have the previvous month value in another measure (yellow)

Orderstock - Poseidon Count - Latest Count = VAR LastestTransfer = CALCULATE( MAX( FactPoseidonOrderStock[StagingDateTime] ), REMOVEFILTERS() ) VAR ReturnValue = CALCULATE( CALCULATE( COUNT( FactPoseidonOrderStock[Location_KEY] ), FILTER( FactPoseidonOrderStock , FactPoseidonOrderStock[StagingDateTime] = LastestTransfer ) ), REMOVEFILTERS( 'Calendar' ) ) RETURN ReturnValue

It is connected to a proper dateTable, Iam just showcasing it by shortMonth

I think my problem is that I dont know how to write the if statement so the visual will get it.

It should be something like:
If MAX(p-stock) is blank for the current month, use PreviousMonthMeasure, else use orginal measure.

Re: Use previous month value if current month value is blank

Whitewater100 — Mon, 05 Sep 2022 22:17:39 GMT

Hi:

This is one way to do it, with a column to show if month is completed(in Date Table) and bring that into the filter pane.

https://drive.google.com/file/d/1dHR_RZfl-YPO-a-kOqK_8_KKLXO-VcRo/view?usp=sharing

I used dummy figures. I hope this helps.

Re: Use previous month value if current month value is blank

Anonymous — Tue, 06 Sep 2022 13:24:11 GMT

I think I fixed it (kind of) with this simple IF statement in the return.

Orderstock - Poseidon Count - Latest Date 2 = VAR ReturnValue = CALCULATE( COUNT( FactPoseidonOrderStock[Location_KEY] ), FILTER( FactPoseidonOrderStock , FactPoseidonOrderStock[StagingDateTime] = MAX( FactPoseidonOrderStock[StagingDateTime] ) ) ) RETURN IF( SELECTEDVALUE( 'Calendar'[Month_ID] ) = MONTH( TODAY() ), [Orderstock - Poseidon Count - Latest Count], ReturnValue )

Orderstock - Poseidon Count - Latest Count
Will always return the latest count (as show in previous post)