cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Current Month Compare To Previous Month based on the given data

Hi,

I would like to seek for your help. I have this data to compare the sales from Current month to Previous month. See my figures below:

From there, I compare the April 2019 to March 2019. And I would like to show on my report this way (see image below):

• The arrow will depends if the amount INCREASE or DECREASE TO/FROM
• The amount is the April Sales Amount

What DAX or Formula should I use? TIA for the Help. I appreciate it.

Stay safe guys!

Thanks and regards,

ECHO

3 REPLIES 3
Highlighted
Super User IX

## Re: Current Month Compare To Previous Month based on the given data

@gericho_diaz_17 - Is that a true representation of your source data?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Super User IX

## Re: Current Month Compare To Previous Month based on the given data

@gericho_diaz_17 , you can measure like with date table marked as date table

``````MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

other
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
``````

For Arrow

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

Highlighted
Helper I

## Re: Current Month Compare To Previous Month based on the given data

Hi,

The outcome is that in purple highlighted photo.

Actually, I only do have problem on creating the arrow or the indicator. Because I cant create a measure or formula/dax that result on the comparison of current and previous month.

Anyway, your links provided is really big help. I appreciate it ! 🙂

Thanks,

Echo