topic Re: Previous Month Revenue Using Fiscal Year and Fiscal Month in DAX Commands and Tips

Previous Month Revenue Using Fiscal Year and Fiscal Month

KMcCarthy9 — Mon, 17 Jun 2024 14:39:18 GMT

I need to calculate month over month of Revenue Earned but using Fiscal Year and Month. I'm at a complete loss how to do this since the normal date/time intelligence does not work.

I have a datetable that holds the fiscal information and a table that holds the revenue data. I *simply* just want to show the previous month value and then conditional formatting for an up or down arrow.

I have attached my .pbix here: Revenue

Thank you in advance!

Re: Previous Month Revenue Using Fiscal Year and Fiscal Month

xifeng_L — Sat, 15 Jun 2024 09:18:57 GMT

Hi KMcCarthy9

The file you provided is corrupted, and cannot be opened.

But I think using the DATESADD function should solve your problem?

Or you can give the month an index number, such as ( month index = year*12 + month ), and then use the current month index -1 to get the previous month.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Previous Month Revenue Using Fiscal Year and Fiscal Month

KMcCarthy9 — Mon, 17 Jun 2024 15:27:53 GMT

xifeng_L Apologies for the corrupt file. I believe it is now fixed: Revenue PBI
I have a fiscal month index column in my datetable. How would I go about using this in my DAX calc?
I tried the following,which has gotten me the closest, but it doesn't appear to give the previous month, as it's giving the same value.

Previous Month Rev = var _rev = CALCULATE(SUM('Revenue Breakdown'[Revenue Earned]), 'Revenue Breakdown'[Vendor Type] = "TMO") RETURN CALCULATE( _rev, DATEADD(vw_AMS_DateTable[CALDAY], -1, MONTH), ALLEXCEPT(vw_AMS_DateTable, vw_AMS_DateTable[FisIndexMonth]))

Re: Previous Month Revenue Using Fiscal Year and Fiscal Month

xifeng_L — Tue, 18 Jun 2024 14:51:59 GMT

Hi KMcCarthy9

You can try below measure.

Previous Month Rev = SUMX( SUMMARIZE('Datetable','Datetable'[FISCYEAR],'Datetable'[FISCMonthNumber],'Datetable'[FisIndexMonth]), VAR CurFISCMonthIndex = 'Datetable'[FisIndexMonth] RETURN CALCULATE( SUM('Revenue'[Revenue Earned]), 'Datetable'[FisIndexMonth]=CurFISCMonthIndex-1, ALL('Datetable') ) )

Demo - Previous Month Rev.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Previous Month Revenue Using Fiscal Year and Fiscal Month

KMcCarthy9 — Tue, 18 Jun 2024 15:01:57 GMT

xifeng_L This works! Thank you so much!