Annual comparison chart

[LuisLOCapelari]

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#M1250...

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

[LuisLOCapelari]

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 parameter

 

[MFelix · Accepted solution]

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.

[LuisLOCapelari]

@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 😁.

[MFelix]

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

[amitchandak]

@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=145s