Forum Discussion
Display latest data compared to earliest data
Hey AngelaB ,
You're off to a great start already! Your approach of identifying the baseline and latest timepoints is logical and works well, especially for someone new to Power BI and DAX. That said, your solution can be improved slightly for performance, readability, and maintainability.
DAX for Include in Graph
Include in graph = VAR CurrentLocation = 'Table'[Location] VAR CurrentTimepoint = 'Table'[Timepoint] VAR MaxTimepoint = CALCULATE( MAX('Table'[Timepoint]), ALLEXCEPT('Table', 'Table'[Location]) ) RETURN SWITCH( TRUE(), CurrentTimepoint = 1, 1, -- Baseline CurrentTimepoint = MaxTimepoint, 2, -- Latest -1 -- Other )Visual Setup Tips:
Use this column (Include in graph) as a visual-level filter, keeping only values 1 (baseline) and 2 (latest).
You can also create a new column or measure to label the timepoints like 'Baseline', 'Latest', and use that in a legend or axis for clarity.
If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.
Best Regards,
Nasif Azam
Hi AngelaB ,
Wanted to start off by saying this is impressive for a novice, Kudos to you!
What I would recommend is turning these into measures rather than calculated columns. Using measures instead of calculated columns is generally a better approach in Power BI for this type of analysis because measures are dynamic and they respond to user interactions, slicers, and filters in real time. This means your logic for identifying the baseline and latest timepoints will always adapt to the current context of the report, without needing to persist static values. Measures also perform better because they are computed on the fly and don't consume additional storage in your data model like calculated columns do.
Here are the columns rewritten as measures:
Max timepoint for location =
CALCULATE(
MAX('Table'[Timepoint]),
ALLEXCEPT('Table', 'Table'[Location])
)Include in graph =
SWITCH(
TRUE(),
'Table'[Timepoint] = 1, 1,
'Table'[Timepoint] = [Max timepoint for location], 2,
-1
)
Another way to optimize your DAX is using the SWITCH() function instead of the nested IF() statements in your Include in graph DAX. The SWITCH() function allows you to add multiple conditions in a clean and easy to read format. I've modified the DAX in the measure above as an example.
If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,
Samson
Thank you SamsonTruong ! I will have a play and see if I can get those to work for me.