Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
61 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |