Taking the MAX Value from Snapshot Data in DAX

[Anonymous]

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 🙂

[v-deddai1-msft]

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

[Anonymous]

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))

[Anonymous]

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])))

[Anonymous]

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?

[lbendlin]

Use the LASTNONBLANKVALUE() function for that.