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   O...
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)

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)