Forum Discussion

hgarcia's avatar
hgarcia
Frequent Visitor
7 years ago
Solved

How to Calculate Prior Month and Prior Year Total

Hello everyone,   I am trying to create a DAX formula that will calculate total revenue for a prior month and year based on our fiscal calendar table, so the date calculations are out of the questi...
Need Help
  • hgarcia's avatar
    hgarcia
    7 years ago

    UPDATE:

     

    i was able to figure it out, here is the end formula...

     

    Prior Month - Revenue - NEW =
    VAR Revenue = IF(SELECTEDVALUE('Months No'[Month No.]) > 1,
    CALCULATE([Invoice Revenue 2], FILTER(ALL('Months No'), 'Months No'[Month No.] = MAX('Months No'[Month No.]) -1)))

    VAR Revenue_2 = IF(SELECTEDVALUE('Months No'[Month No.],BLANK()),
    IF(SELECTEDVALUE('Months No'[Month No.]) = 1,
    CALCULATE([Invoice Revenue 2], FILTER(ALL('Time Calculations'), 'Time Calculations'[Time.Fiscal Year] = MAX('Time Calculations'[Time.Fiscal Year]) -1),'Time Calculations'[Fiscal Period Number] = 12),"-"))

    RETURN

    IF(SELECTEDVALUE('Months No'[Month No.]) = 1, Revenue_2, Revenue)
hgarcia's avatar
hgarcia
Frequent Visitor
7 years ago

UPDATE:

 

i was able to figure it out, here is the end formula...

 

Prior Month - Revenue - NEW =
VAR Revenue = IF(SELECTEDVALUE('Months No'[Month No.]) > 1,
CALCULATE([Invoice Revenue 2], FILTER(ALL('Months No'), 'Months No'[Month No.] = MAX('Months No'[Month No.]) -1)))

VAR Revenue_2 = IF(SELECTEDVALUE('Months No'[Month No.],BLANK()),
IF(SELECTEDVALUE('Months No'[Month No.]) = 1,
CALCULATE([Invoice Revenue 2], FILTER(ALL('Time Calculations'), 'Time Calculations'[Time.Fiscal Year] = MAX('Time Calculations'[Time.Fiscal Year]) -1),'Time Calculations'[Fiscal Period Number] = 12),"-"))

RETURN

IF(SELECTEDVALUE('Months No'[Month No.]) = 1, Revenue_2, Revenue)