Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
I am currently struggling to calculate a DAX value base on previous matrix row. Here is an example shows what I am tring to do.
Imagine this is the matrix I created in the BI, I am trying to calculate column D use DAX, 166 is a fix number for 2020-04-01, but everything below that is base on previous row. Like D2 for 2020-05-01 row is D1+B1+C1, and D3 for 2020-06-01 is base on calculated D2 number plus B2 and C2. column B and C also calculated by DAX.Could anyone help me on this?
I really preciate your help!
Solved! Go to Solution.
You should really consider restructuring your data @TinaL22 and when you see the answer here is why. this is the measure that works - add this to a Matrix visual.
New Total =
VAR varCurrentDate = MAX(Data[Date])
VAR varMinDate =
CALCULATE(
MIN(Data[Date]),
REMOVEFILTERS(Data[Date])
)
VAR Result =
CALCULATE(
SUM(Data[Column1]) + SUM(Data[Column2]) + SUM(Data[Column3]),
FILTER(
ALL(Data),
Data[Date] <= varCurrentDate
)
) -
CALCULATE(
SUM(Data[Column1]) + SUM(Data[Column2]),
FILTER(
ALL(Data),
Data[Date] = varMinDate
)
)
RETURN
IF(
MAX(Data[Date]) = varMinDate,
MAX(Data[Column3]),
Result
)
It returns this:
Which is the answer you want, but there are some issues.
So while this works, it isn't ideal and isn't terribly flexible. If you can figure out how to restrcutre your data into a more normalized table where the logic flows that whatever you put in the "New Total" value is consistent formula without special handling for just the first row of the data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCan you just change the look of the date?
Additionally, you an set custom formats per this article.
You cannot do calculations based on text data. If you must have a text field, then you need to add a new column in Power Query that converts the date to the Text format you want and show that in the visual. You could then remove the actual date. The measure would still work. But I don't recommend that process unless absolutely necessary. Changing the format is the best way to go here IMHO.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou should really consider restructuring your data @TinaL22 and when you see the answer here is why. this is the measure that works - add this to a Matrix visual.
New Total =
VAR varCurrentDate = MAX(Data[Date])
VAR varMinDate =
CALCULATE(
MIN(Data[Date]),
REMOVEFILTERS(Data[Date])
)
VAR Result =
CALCULATE(
SUM(Data[Column1]) + SUM(Data[Column2]) + SUM(Data[Column3]),
FILTER(
ALL(Data),
Data[Date] <= varCurrentDate
)
) -
CALCULATE(
SUM(Data[Column1]) + SUM(Data[Column2]),
FILTER(
ALL(Data),
Data[Date] = varMinDate
)
)
RETURN
IF(
MAX(Data[Date]) = varMinDate,
MAX(Data[Column3]),
Result
)
It returns this:
Which is the answer you want, but there are some issues.
So while this works, it isn't ideal and isn't terribly flexible. If you can figure out how to restrcutre your data into a more normalized table where the logic flows that whatever you put in the "New Total" value is consistent formula without special handling for just the first row of the data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you so much, this is really helpful.
Glad I was able to assist @TinaL22 !
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans, If you don't mind I ask you one additional question on this. So right now based on your measure I calculated the last column, which is great. But right now I want to change column A from date format to text. For example, 2020-04-01 shows Apr-20, 2020-05-01 shows May-20. After the changes the measure stope working, which I understand in the measure it compares the date, not the text. So I wondering if you have any idea how to make it work. Thank you so much.
Can you just change the look of the date?
Additionally, you an set custom formats per this article.
You cannot do calculations based on text data. If you must have a text field, then you need to add a new column in Power Query that converts the date to the Text format you want and show that in the visual. You could then remove the actual date. The measure would still work. But I don't recommend that process unless absolutely necessary. Changing the format is the best way to go here IMHO.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThen I feel I just change the look, that will be the easiest solution, thank you so much👍
Glad to help @TinaL22
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |