Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Use previous month value if current month value is blank

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

Jan5339951528
Feb556430986
Mar62412391863
Apr63913051944
May72213422064
Jun91511992114
Jul90111682069
Aug88911492038
Sep887 887


This is the visuals

DAX
  • Anonymous's avatar
    Anonymous
    4 years ago

    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)

5 Replies

  • colacan's avatar
    colacan
    Icon for Resolver II rankResolver II
    4 years ago

    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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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
  )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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)