cancel
Showing results for
Did you mean:
New Member

## Paginated Reports - Calculate Previous month same year sales

Hi All,

I am having sales & sales_previous columns and date parameter. If I select May 5th, 2020 in parameter, it should display same date sales in sales column and same date previous month sales should display in sales_previous column.

How can we achieve previous month sales in sales_previous column. Can anyone help me to achieve this scenario in paginated reports.

Regards,

Sudheer D

5 REPLIES 5
Super User IV

@Dsudheer , With Date table and time intelligence

examples

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

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

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

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))
``````

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!

New Member

Hi Amit,

All DAX functions are not supported in Power BI Report Builder. Calculate & PreviousMonth functions are not available in report builder.

I need the solution in Power BI Report Builder not in Power BI Desktop.

Regards,

Sudheer D

Super User IV

@Dsudheer - So you could try the time intelligence function PREVIOUSMONTH.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

But, in general, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

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!

New Member

Hi Greg,

All DAX functions are not supported in Power BI Report Builder. Calculate & PreviousMonth functions are not available in report builder.

I need the solution in Power BI Report Builder not in Power BI Desktop.

Regards,

Sudheer D

Helper II

Hi @Dsudheer
Did you find the solution for calculating MTD measures in report builder?

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.