CODEWITHSUNDEEP
sumdaxcalculated-fieldcumulative-sum
salvationishere
salvationishere

Reputation: 3511

DAX Year over Year in line chart

I'm trying to write a DAX expression for calculating the year over year in a line chart. The problem is that my DAX expression is resulting in a flat line and not calculating it as one day's payment amount over the TOTAL payment amount. e.g. In this example, I'm only trying to display only one month. When I use a Date column as my Axis, it displays correctly as below (CRD). But when I use a Text column representing the day, it displays a flat line. (My code for CRD Day is FORMAT([CRD],"dd")). enter image description here enter image description here

I also created these two measures and displayed them in this table which shows that it is not using the TOTAL payment amount for the denominator.

MTD RT % = [MTD Running Total] / [Total Payment Amount]
MTD RT2 % = CALCULATE(SUMX(VALUES(Cash_Goals_Cash_Velocity[CRD]), [MTD Running Total] / [Total Payment Amount]),ALL(Cash_Goals_Cash_Velocity[net_payment_amount]))
MTD Running Total = TOTALMTD(SUM([net_payment_amount]),Cash_Goals_Cash_Velocity[CRD])
Total Payment Amount = CALCULATE(SUM(Cash_Goals_Cash_Velocity[net_payment_amount]),ALL(Cash_Goals_Cash_Velocity[CRD]))

This table looks like below. What am I doing wrong? How can I do this in Power BI? enter image description here

Upvotes: 0

Views: 312

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I think you don't require [CRD Day] column at all. Just change the settings for presentation in the visualizations properties.

enter image description here

Remove Year, Quarter and Month leaving only Day.

You will get something like the below image using this DAX expressions:

Running Total = CALCULATE(SUM([Payment]),FILTER(ALL(Table),Table[CRD] <= MAX([CRD]) ))

MTD RT % = [Running Total] / [Total Payment Amount]

enter image description here

With your running total measure should work too.

Upvotes: 0

Related Questions