topic Re: Calculated column for summing last years margin to date, against the organisation in DAX Commands and Tips

Calculated column for summing last years margin to date, against the organisation

Rogerh — Wed, 26 Aug 2020 15:00:26 GMT

Good afternoon,

I created a table of our customers using Calculated Columns and have been adding additional calculated columns to it so I can easily write IF statements based on the results. All has been fine except for trying to create a column on Margin, same period last year, to date for 2019.

This code works fine for this year:

Year to Date Margin =

CALCULATE (

SUM('Platform - Orders'[New Margin]),

FILTER ( ALL ( 'Platform - Orders' ), 'Platform - Orders'[organisation_id] = 'Platform - Scorecard'[organisation_id] && 'Platform - Orders'[Scheme Month] >= DATE(2020, 1, 1)))

Just to mention I use the Organisation ID to match the custom table and the 'Platform - orders' table.

I used the same logic as above for 2019, the code didn't error but it returns nothing.

2019 YTD Margin =

CALCULATE (

SUM('Platform - Orders'[New Margin]),

FILTER ( ALL ( 'Platform - Orders' ), 'Platform - Orders'[organisation_id] = 'Platform - Scorecard'[organisation_id] && 'Platform - Orders'[Scheme Month] >= DATE(2019,01,01) && 'Platform - Orders'[Scheme Month] <= DATE(2019,08,01)))

I looked into it and Dates between came up a lot so I gave that a go in multiple ways, with no results.

2019 YTD Margin =

CALCULATE (

SUM('Platform - Orders'[New Margin]),

FILTER ( ALL ( 'Platform - Orders' ), 'Platform - Orders'[organisation_id] = 'Platform - Scorecard'[organisation_id] && DATESBETWEEN('Platform - Orders'[Scheme Month] , DATE(2019,01,01) , DATE(2019,08,01))))

Ideally I would like this to be automated so each month I dont have to update the date but I do not know how to do this. Can I please have some assistance with the code and / or guidence on the best way of doing it?

Thanks

Roger

Re: Calculated column for summing last years margin to date, against the organisation

amitchandak — Wed, 26 Aug 2020 16:05:08 GMT

Rogerh , Try with a date table

measure =
var _max = maxx(allselected(Date),Date[Date]) // or use today
var _min = date(year(_max)-1,1,1)
return
CALCULATE (
SUM('Platform - Orders'[New Margin]),filter(all(Date), Date[Date]>=_min && Date[Date]<=_min))

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Calculated column for summing last years margin to date, against the organisation

Rogerh — Thu, 27 Aug 2020 14:43:00 GMT

Hi amitchandak

Thank you for your reply. Sorry I should have mentioned I am unable to use a date table for this column. Basically our 'scheme month' runs from different days of the month. For example: the 28th to the 27th of the following month. The dates can change from month to month.

Each order has the scheme month against it, for example all orders between July the 28th and August the 27th will have the scheme month of 01/08/2020 against it.

Thanks

Roger