Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello community!,
I am having a hard time figuring out a dynamic DAX that will result in current week values and also a DAX for previous week so I can do a comparison.
I have tried a few things but somehow the moment I want to filter by state everything gets jumbled up. Could someone please assist? Thank you!
This is what is happening:
These are the codes I have tried:
Hi, I am trying to get the week on week difference, but nothing works, here a few options I tried, can anyone help me to fix this !! Great help as I am stuck
1-
2-
OMG thanks for this advise- extremely helpful!
// Please note that Date should be
// the date table in your model marked
// as such and joined to your fact table(s)
// on the Date field. This table should have
// fields to move through a fiscal calendar.
// There should be a field in the date table
// called FiscalWeekId storing consecutive(!)
// integers that would number all the fiscal
// weeks. This column would, of course, be
// hidden as it's only an auxiliary one for
// doing calculations.
// Here's what your measure would look like:
[Last Week's LSAR] =
var __onlyOneWeekIsVisible = HASONEVALUE( 'Date'[FiscalWeekID] )
var __fiscalWeek = SELECTEDVALUE( 'Date'[FiscalWeekID] )
var __result =
If( __onlyOneWeekIsVisible,
CALCULATE(
[LSAR],
'Date'[FiscalWeekID] = __fiscalWeekID - 1
ALL( 'Date' )
)
)
return
__result
// This measure colud be written in a shorter
// form at the price of readability (but it
// might be faster):
[Last Week's LSAR] =
var __fiscalWeek = SELECTEDVALUE(
// Let's say that these ID's start
// do not start at -100 in your table.
// Bear in mind that unknown weeks
// would be marked in a such a table customarily
// as having an ID of -1.
'Date'[FiscalWeekID],
-10
)
var __result =
CALCULATE(
[LSAR],
'Date'[FiscalWeekID] = __fiscalWeekID - 1
ALL( 'Date' )
)
return
__result
@cuohanele , refer how to create week wise calendar
Work on Week Vs Week using Rank
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-La...
Example
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
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-Y...
Appreciate your Kudos.
@cuohanele - You might try implementing a sequential week number to make things easier.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116
Also, these might help:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487#M123
https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128
Hi @cuohanele ,
Please check this blog
Objective: We would like to have WTD(Week Till Date) Last WTD and This Week vs Last Week.
Dataset: We have taken Sales data. The data is from April 2018 till April 2020. We have created a date Calendar.
Pbix is attached to the blog.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |