Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dinovic
Frequent Visitor

Lastdate current month. Compare revenue to corresponding date last month.

I have a measure of Revenue for the current month. The revenue is cumulative, hence the last day revenue is my MTD calculation.

In order to be able to perform month to month comparison I would need to find the corresponding "last date" from previous month. I.e. if the lastday measure below return 11th of April, I would like the previous month measure to return 11th of March. Tried with parrarelperiod function but without any results. 

All help appreciated,

thanks.

 

lastday = LASTDATE('sales'[dateCreated])

Revenue Current Month = CALCULATE(SUM('sales'[revenue]) , DATESBETWEEN('sales'[dateCreated].[Date],[lastday],[lastday]))

Revenue Previous Month = ?

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @dinovic,

 

According to your description above, you should be able to use Could you try the formula below to see if it works in your scenario?

 

Revenue Previous Month =
CALCULATE (
    [Revenue Current Month],
    DATEADD ( 'sales'[dateCreated], -1, MONTH )
)

In addition, based on my understanding, the DATEBETWEEN function is not necessary here in your [Revenue Current Month] measure, the formula below should also works. Smiley Happy

 

Revenue Current Month = CALCULATE ( SUM ( 'sales'[revenue] ), 'sales'[dateCreated] = [lastday] )

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Employee
Employee

Hi @dinovic,

 

According to your description above, you should be able to use Could you try the formula below to see if it works in your scenario?

 

Revenue Previous Month =
CALCULATE (
    [Revenue Current Month],
    DATEADD ( 'sales'[dateCreated], -1, MONTH )
)

In addition, based on my understanding, the DATEBETWEEN function is not necessary here in your [Revenue Current Month] measure, the formula below should also works. Smiley Happy

 

Revenue Current Month = CALCULATE ( SUM ( 'sales'[revenue] ), 'sales'[dateCreated] = [lastday] )

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.