cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

Compare values change and percent change for selected months only

Trying to recreate this excel summary table and wonder what dax calculation/s would make that possible? I tried some offset calcs, and few other options, but I assume I am not familiar with dax to find solution for this?

I have only one data table named "Query1", which has millions of rows for several dozens of metric/attribute in columns. I am only using 4 metrics here as an example, with actual numbers. My date column comes as text in Query1 so I created calculated column to use for my dates, named as "Posting Date Short". Here's a few pictures that might help for the reference:

My original Date column as text:

My calculated date columns name I use in my visuals:

Final table I would like to recreate: (Mainly how can I calculate or create some new measures for the yellow highlighted cells, \$Change, %Change), which should work interactively in case I use some filters, etc. Basically current month values compared to 3 months ago and the same month one year ago. Any suggestions would be much apprecited!

2 REPLIES 2
Super User

@Mk60 , Connect date of you table with date if date table and then you can have measures like

current = SUM(Sales[Sales Amount])

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Change QOQ = [current]- [trailing QTR]

Change QOQ% = divide([current]- [trailing QTR],[trailing QTR])

Change YOY = [current]- [Year behind Sales]

Change YOY% = divide([current]- Year behind Sales],[Year behind Sales])

for the measure's header grouping consider the calculation group. They can now be created in power bi desktop

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

Model explorer public preview with calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU

Resolver I

Hi Mr. amitchangak,

First, I just wanted to thank you for your time and consideration here! So much appreciate you, not surprised you're a supersuer. Before I try your suggested calcs here, I need to ask you if using your syntax above absolutely requires having an existing Date table? My Query1 is very basic data table and only one column has dates values, (Posting Date as attached in my picture above). If that is requirement to use your calculations, I would not mind to try to build Calendar table, since I heard good things about having one, and it would be another opportunity for me to learn something new anyway. So, is the Date table MUST have in order to use your calcs above? Thanks so much once again!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

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

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors