Forum Discussion
Annual comparison chart
Hello everyone, I'm Luis, Brazilian. I would like help creating a graph in which I can compare two years in two lines. I'm analyzing a company's revenue and I need to compare a desired year with any other year.
I have to analyze this at the quarter, month and day level. I need to show the accumulated revenue, but it needs to respect the context analyzed. For example, if I didn't filter a specific month, it's to calculate the accumulated year, but if I selected a month I need it to start from zero, calculating only in the interval of the selected month (or months).
I already have a measure that works for the main year, but now I need a measure that works for the other year to be compared, which will be selected in a parameter slicer.
The measure for the main year I obtained based on another topic of mine here on the forum: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Rank-filtered-date/m-p/3335809#M125096
This is the link to access the example report (I haven't learned how to attach it here yet)
https://drive.google.com/file/d/1rRtYjY42SODJCqC3avE8jUMX8Rigdo21/view?usp=sharing
Hi LuisLOCapelari ,
Try the following code:
Medida Dinamica Outro Ano Acumulado = var AnoComparativo = [Valor Parâmetro] RETURN CALCULATE([Medida Dinamica Comparativo Acumulado], DIM_DATA[Ano] = AnoComparativo , REMOVEFILTERS(DIM_DATA[Ano]))See file attach.
6 Replies
LuisLOCapelari , Assuming you have a separate filter for date and year coming from the date table
You can have measure like
M1=
Var _fil = calculate(isfiltered(Date[Month]), allselected(Date))return
if(_fil, calculate([Your Measure], datesmtd(Date[Date]) ) , calculate([Your Measure], datesytd(Date[Date]) ) )
YTD when only year us selected.
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sHi LuisLOCapelari ,
try the following code:
YTD Sales = CALCULATE([Valores], FILTER( ALLSELECTED('DIM_DATA'), DIM_DATA[Data] <= MAX(DIM_DATA[Data]) && YEAR(DIM_DATA[Ano]) = YEAR(MAX(DIM_DATA[Ano])) ) )Hello MFelix amitchandak , thank you very much for your help. Unfortunately, your solutions were not enough to solve the problem.
Returning to the explanation:
- I want to compare the company's revenue in any two years
- Both years used in the comparisons are selected in slicers
- The main year is selected in a date slicer from the date table (DIM_DATA)
- The year to be compared is selected in a slicer from a parameter table
- Both measures will be used in a line graph, which contains a drill (up/down) in the date segmentations (year, quarter and month) on the X axis
- The accumulated calculation for the main year is working ([Medida Dinamica Comparativo Acumulado])
- I am having difficulty creating the calculation of the accumulated revenue for the year to be compared ([Medida Dinamica Outro Ano Acumulado])
- The problem is that the secondary measure considers the date context (year, quarter and month), that is, if I filter a specific quarter (or more) in the slicer, only the accumulated in that period of quarters should be considered, but in the year informed by parameterHi LuisLOCapelari ,
Try the following code:
Medida Dinamica Outro Ano Acumulado = var AnoComparativo = [Valor Parâmetro] RETURN CALCULATE([Medida Dinamica Comparativo Acumulado], DIM_DATA[Ano] = AnoComparativo , REMOVEFILTERS(DIM_DATA[Ano]))See file attach.
MFelix Thank you very much for your help and for taking the time. Now it works as I wanted. It was easier than I thought 😁.
You're on the right track looking to visualize annual revenue side by side — creating an effective comparison chart is crucial for year-over-year analysis.
