YTD for multiple measures in matrix

[narchans4u]

Hi,

I am trying to achieve YTD calculation in a matrix (that shows Campaign_name in row and Month-year in column and has multiple values (measures - each one for Paid, one for Active, another for Contacted and so on - each has its calculation in backend - being handled at SQL view level) - as below:

 

Now I need to calulate YTD for Paid,Active,Contacted,Quoted(each value) - and show it as column total - shown as below:

 

I tried YTD calculation in DAX as follow:

YTDPaid = TOTALYTD(count('Table1'[Paid]),[CreatedDate].[Year])

YTDActive = TOTALYTD(count('Table1'[Active]),[CreatedDate].[Year])

and so on - for each measure - but that doesn't give me expected result of having a YTD calculation on column - for each measure/value. I need one YTD calc for all values - as column total.

Any one tried/achieved anything similar to this? Please help

Regards,

Archana

[v-jiascu-msft]

Hi Archana,

The TOTALYTD iterates over the dates. So we need feed it with dates rather than years. Please try it like below. The [CreatedDate] should be from the same table with columns of Matrix.

YTDPaid = TOTALYTD(count('Table1'[Paid]),[CreatedDate])


YTDActive = TOTALYTD(count('Table1'[Active]),[CreatedDate])

Best Regards,

Dale

[narchans4u]

Hello,

thanks for your reply.

But with above calculation - I need to write 4+ calculations - one for paid, one active and so on for each status and I will end up placing it in rows - which gives results like this:

Expectation is this:

Each status which has different calculation i.e. different columns in DB view - Paid,Active,Contacted,Quoted -- set to 1 or 0 and am summing it up in report level) - there's no "status" column as such in DB (should I make one? and bring in data in a different way altogether?)

I want to achieve 1 calc for all the status which changes based on the column/metric (status) in rows