Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

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

 

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

MFelix_0-1730299396303.png

See file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5

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

problema acumulado 1.png

problema acumulado 2.png

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

MFelix_0-1730299396303.png

See file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.