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 everyone,
We have requirement in our project such that based on the 'To' date selected in the period selector, we need to show last 4 weeks data alone in the chart. Supposing we are at the 47th Week, then the chart should display only values for 44 to 47th week. If the 'To' date selected falls under 44th week, the data displayed should be for 41st to 44th week
We have the start and end date of every week defined in the Cube as well. We are following Gregorian Week (Mon-Sun).
I had created a couple of DAX Measures which will calculate sum of last 4 weeks based on the MAX of Date selected, as shown below:
Solved! Go to Solution.
Ideally you would filter this data out before it gets loaded into Power BI via the Power Query engine, but you mentioned that your data is coming from a cube, so if it is SSAS, then you cannot use Power Query.
You have two possible option (there are probably a dozen ways to go about it, but these two are straightforward):
Weeks Ago = WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2)
4 Week History = CALCULATETABLE( Sales, FILTER(Sales,WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2) <= 4) )
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIdeally you would filter this data out before it gets loaded into Power BI via the Power Query engine, but you mentioned that your data is coming from a cube, so if it is SSAS, then you cannot use Power Query.
You have two possible option (there are probably a dozen ways to go about it, but these two are straightforward):
Weeks Ago = WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2)
4 Week History = CALCULATETABLE( Sales, FILTER(Sales,WEEKNUM(TODAY(),2) - WEEKNUM(Sales[Date],2) <= 4) )
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |