Current and Prior Period Measures

DAX DAX Calculations
1

Having difficulty also creating formulas to create singular measures for:

• Current Month Loan Originations
• Prior Month Loan Originations
• Current Quarter Loan Originations
• Prior Quarter Loan Originations
• Current Year Loan Originations
• Prior Year Loan Originations

I’d like to show each measure as a card in my dashboards without creating calculated columns which flag if the month is current or prior.

Has anyone done this by chance?

Thank You,
Nick

2

Here’s a few formulas around this. You’ll have to sub in the correct ‘Loan Originations’ measure

None of the below formula require any calculated columns and are really suited just for cards, nothing else really

Current Month Sales = 
VAR CurrentMonth = MONTH( TODAY() )
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[MonthOfYear] = CurrentMonth && Dates[Year] = CurrentYear ) )


Prior Month Sales = 
VAR CurrentMonth = MONTH( TODAY() )
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        IF( CurrentMonth = 1,
        Dates[MonthOfYear] = 12 && Dates[Year] = CurrentYear - 1,
        Dates[MonthOfYear] = CurrentMonth - 1 && Dates[Year] = CurrentYear ) ) )


Current Year Sales = 
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[Year] = CurrentYear ) )


Prior Year Sales = 
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[Year] = CurrentYear - 1 ) )


Current Quarter Sales = 
VAR CurrentQuarter = LOOKUPVALUE( Dates[QuarterOfYear], Dates[Date], TODAY() )
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[Year] = CurrentYear && Dates[QuarterOfYear] = CurrentQuarter ) )


Prior Quarter Sales = 
VAR CurrentQuarter = LOOKUPVALUE( Dates[QuarterOfYear], Dates[Date], TODAY() )
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        IF( CurrentQuarter = 1,
        Dates[Year] = CurrentYear - 1 && Dates[QuarterOfYear] = 4,
        Dates[Year] = CurrentYear && Dates[QuarterOfYear] = CurrentQuarter ) ) )

This should do it for all those.

Thanks

3

Brilliant!! Thank you!!

4

Seems that perhaps the Prior Quarter calculation is off. It’s not clear to me how to fix it. Would you happen to know why it’s off? It appears to be returning far less than it should.

5

Hard to really say because I can’t see the values.

What is the difference?

Also have you tried breaking out each part of the formula. Just double check the CurrentQuarter is evaluating correctly for every row.

6

Instead of the LOOKUPVALUE for current quarter

Try this as the variable formula instead

SELECTEDVALUE( Dates[QuarterOfYear] )

7

Prior Quarter using LOOKUPVALUE produces less than Prior Month. Prior Quarter and Current Quarter return the same value using the same function. If I sub in the SELECTEDVALUE function, then the result is null.

Thank You,
Nick

8

Sorry I got confused. The original formula is correct, there was no need to change it.

It look like all you need to do actually is adjust the below

Prior Quarter Sales = 
VAR CurrentQuarter = LOOKUPVALUE( Dates[QuarterOfYear], Dates[Date], TODAY() )
VAR CurrentYear = YEAR( TODAY() )

RETURN
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        IF( CurrentQuarter = 1,
        Dates[Year] = CurrentYear - 1 && Dates[QuarterOfYear] = 4,
        Dates[Year] = CurrentYear && Dates[QuarterOfYear] = CurrentQuarter - 1 ) ) )

Just make sure to add the minus 1 to the end of the formula.