Compare values change and percent change for selected months only

[Mk60]

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!

[amitchandak · Accepted solution]

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

current = SUM(Sales[Sales Amount])

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

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

[Mk60]

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! 

[Mk60]

Just started working on this now as I finally created date table in my model, and only wanted to make sure I thanked you for your suggestions, since those worked perfect now once I have date table in place. Thans much once again, even liitle late!