Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
I looked into it and Dates between came up a lot so I gave that a go in multiple ways, with no results.
@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-Y...
Appreciate your Kudos.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |