DAX to display Current period, Yearly total and YTD Running total

[smpa01]

Hello Experts,

My source data is following

Name	CAT	Quarter	Amount
Name1	CAT1	1	1000
Name1	CAT1	2	2000
Name1	CAT1	3	3000
Name1	CAT1	4	4000
Name1	CAT2	1	100
Name1	CAT2	2	200
Name1	CAT2	3	300
Name1	CAT2	4	400
Name2	CAT1	1	10000
Name2	CAT1	2	10000
Name2	CAT1	3	10000
Name2	CAT1	4	10000
Name2	CAT2	1	20000
Name2	CAT2	2	20000
Name2	CAT2	3	20000
Name2	CAT2	4	20000

I am trying to create a matrix visual with this data which will be filtered on Quarter #. When filtered it will show 3 values for amount

A. Current quarter value - easily achievable,

B. Yearly Total - DAX to ignore the Quarter filter and calculate the sum of amount

C. YTD Running total - DAX to respect the Quarter Filter and calculate the sum of amount for all period <=Quarter Filter

I am currently generating the following which satisfies A

How can I generate the folowing

Name	CAT	A	B	C
Name1	CAT1	3000	10000	6000
Name1	CAT2	300	1000	600
Name2	CAT1	10000	40000	30000
Name2	CAT2	20000	80000	60000

Thank you in advance.

[TomMartens · Accepted solution]

Hey,

I created these 3 measures:

A - Current quarter value = 
SUM('Table1'[Amount]) 

B - Yearly total = 
CALCULATE(
    [A - Current quarter value]
    ,ALL('Table1'[Quarter])
) 

C - YTD Running Total = 
CALCULATE(
    [A - Current quarter value]
    ,FILTER(
        ALL(Table1[Quarter])
        ,'Table1'[Quarter] <= MAX('Table1'[Quarter])
    )
)

These measures allow me to create a matrix visual and a table visual like so:

Instead of using the quarter column as a viual level filter I created a slicer.

Even if it's not necessary today, because your data model has just one table, I recommend to read through this article: https://www.daxpatterns.com/time-patterns/ From my experience it's always a good idea to have a dedicated Calendar table, even if it seems to make things more complicated at a first glance.

Regards,

Tom

[smpa01]

@TomMartensthanks a lot for your help. It was urgent and I am so greatful to you and this community. It is really awesome.

My DAX side is not as strong as M. Can you please advise if there are some resources I can follow to learn DAX structurally and pick up the basic DAX concepts.

Thanks again. I picked my brain all morning for this.

[TomMartens]

Hey @smpa01 

my favorite recommendation to start learning DAX is this book:

https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766/ref=sr_1_1?keywords=Beginni...

by @Phil_Seamark 

Regards,

Tom

[parry2k]

@smpa01 there are lot of posts on time intelligence calculations and also lot of dax functions available, first and foremost, it is best practice to have date dimension in your model (lot of post on this as well) and then all these calculations become super easy.

[smpa01]

@parry2k  thanks. Can you please post some URL for date dimension tables here if you happen to have them handy by chance.