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.
Hello
I'm currently trying to create a Dashboard and visualise Weekly Snapshotted data over time with the data SUMing. The data I'm using is snapshotted at a weekly level and stores against the Saturday of every week and I have a date hierarchy of Day, Week, Period, Half and Year. Because the data is a snpashot I want to create a DAX Measure that will take the last snapshot Value (based on the Date hierarchy) instead of PBI SUMing the data together
Ultimtely I want to create a Line chart Visual with the above Date Hierarchy so the user can drill up and down within the visual without the data being summed as the user does.
Any help with the DAX to do this would be great appericated. Below is an example of how the metric would look in a table
Week 1 - 4125 Period 1 - 4099 Half 1 - 4111
Week 2 - 4110 Period 1 - 4099 Half 1 - 4111
Week 3 - 4085 Period 1 - 4099 Half 1 - 4111
Week 4 - 4099 Period 1 - 4099 Half 1 - 4111
Week 5 - 4066 Period 2 - 4111 Half 1 - 4111
Week 6 - 4101 Period 2 - 4111 Half 1 - 4111
Week 7 - 4111 Period 2 - 4111 Half 1 - 4111
Thanks 🙂
Hi @Anonymous ,
Just use the following meaure :
Periodvalue = LASTNONBLANKVALUE(Date[Week],MAX(Facttable[value]))
Halfvalue = LASTNONBLANKVALUE(Date[Period],[Periodvalue])
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hello
Unfortunitly this doesn't work. I need to SUM the value up in the table and then table the MAX of the final Week SUM
This is what I've tried but keep getting the error "The MAX Function only accepts a column reference as an arguement". Any ideas or help would be great 🙂
Metric =
VAR Variable = LASTNONBLANKVALUE(Dates[Ops Week],[SUM(Emp])
RETURN
LASTNONBLANKVALUE(Dates[Ops Week],MAX(VARIABLE))
Hello
I've tried using lastnonblank value but can't get it to work correctly. I need to Sum the Metric and then get the MAXED value from the last weeks data. My data is at a very low granuality and need to be aggregated before Maxed
I've tried this but is doesn't like the MAX in the calculate
Metric = LASTNONBLANKVALUE(Dates[Date],CALCULATE(SUM(Fact_Table[ID]),Dates[Date] = MAX(Dates[Date])))
Hello
I've give these a try and let you know. How would these work using a hierarchy ?
Ideally I want to create a Line Chart Visual with a Week/Period/Quarter hierarchy and this metric and allow the users to move between aggregations with the data being summed?
Use the LASTNONBLANKVALUE() function for that.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |