Forum Discussion
YoY Variance Visualization with data on unique tables
Hi,jfrench I am glad to help you.
Based on your description, you want to calculate the change in PROJECTS for each quarter between two years
For example the number of projects in the first quarter of 2024 is down 67% compared to 2023
If I understand you correctly, you can refer to my test below
I have constructed three measures and show them on a line chart visual.
like this：Here is the DAX code
M_preProAmount = CALCULATE( COUNT('2023_Table'[Project LOE]),FILTER(ALL('2023_Table'),'2023_Table'[Fiscal Quarter]=MAX('2023_Table'[Fiscal Quarter])))M_thisProjectAmount = CALCULATE( COUNT('2024_Table'[Project LOE]),FILTER(ALL('2024_Table'),'2024_Table'[Fiscal Quarter]=MAX('2024_Table'[Fiscal Quarter])))M_result = DIVIDE([M_thisProjectAmount]-[M_preProAmount],[M_preProAmount],0)I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,jfrench .Thank you for your reply.
According to your description, you didn't get the correct result when trying the test code I gave you, which may be caused by the difference of the computing environment (in fact, the data I tested is very simple and can't completely replace your real computing environment)
Please check if the visual in which the measure is used is affected by other filters (such as slicers or field filters) and write the DAX code according to your real situation.
Also if you want to show the data of the same field in two tables in one visual, it's not as convenient as combining two tables into one (you may need to write more than one measure).
If you could provide more data about this, it would be very helpful to solve your problem.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
I identifed where my mistake was and have now accepted your comment as solution.
If I were to want to add more than 1 data point to slice data, would I add that new column data point after the =MAX in formula? In addition to the visuals created above I also want to display progress or momentum of a project. I have a column called status with values such as 'planning', 'completed', 'backlog. How can I add that into my existing formula?
Hi,jfrench
Thank you for your reply, in fact I am using the MAX function in the measure to group and aggregate the results by Year to calculate the number of projects for each quarter of the year. If you have another column called Status in your environment, you can also try to group and aggregate the data again in the same way
like this:M_preProAmount = CALCULATE( COUNT('2023_Table'[Project LOE]),FILTER(ALL('2023_Table'),'2023_Table'[Fiscal Quarter]=MAX('2023_Table'[Fiscal Quarter], '2023_Table'[Status]=MAX('2023_Table'[Status])))Of course, you can also use status as an external filter by adding a slicer, which will have the same effect.
The code might look like this.
M_thisProjectAmount = CALCULATE( COUNT('2024_Table'[Project LOE]),FILTER(ALLSELECTED('2024_Table'),'2024_Table'[Fiscal Quarter]=MAX('2024_Table'[Fiscal Quarter])))The ALLSELECTED function calculates the options in the external slicer as well as the filters in the measure.
Note that you need to consider your real computing environment, depending on your actual situation, the code I gave may need to be modified, I hope my reply can solve your doubts.