Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
narchans4u
Frequent Visitor

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:

 

 Matrix.PNG

 

MatrixRC.PNG

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

 

ExpectedMatrix.PNG 

 

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

2 REPLIES 2
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

image.png

 

Expectation is this:

ExpectedMatrix.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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