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.
Hi all,
I am trying to create a matrix that stores a number of measures the users need to see TY vs. LY. I have created standalone TY and LY (this is to date, e.g. for an accurate mid-week comparator) measures which work, however, due to the amount of data involved/I want to show at one time, I need to show a slicer where the user selects a week number. This breaks the Sales (LY) figure from pulling through as that week number doesn't match to what is specified in the slicer, resulting in a value showing for TY but not the LY.
Is there a way I can get this to pull the LY WTD figure through, too?
I have a calendar table built at day level which is joined to my order table.
Thanks in advance.
HI @Anonymous,
It should help if you provide some dummy data, expected results with a similar data structure that same as your raw data tables.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@Anonymous , With Date table , Rank and Week number
WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))
Refer for WTD and Last WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
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/
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.
The 2nd formula when I put and apply in my .pbix brings the entirety of LY's data through into the matrix, should it not only be pulling through LY to date?
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |