Forum Discussion

narchans4u's avatar
narchans4u
Frequent Visitor
8 years ago

YTD for multiple measures in matrix

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

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      narchans4u
      Frequent Visitor
      8 years ago

      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