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.
I have a summarized table with contains multple columns like product group and customer as well as as of date column. Associated with the as of table column is a flow date column which has the 'n' number of days in a given month for each as of date. I need to calculate the daily aggregate value of the Actual column and show the daily change from the prevoius day. I wrote a DAX query but it does not work. Any help is appreciated. Dax formula is below and the smaple data is attached.
Solved! Go to Solution.
Hi, @sridharpolina
You may try the following calculated column to see if it helps.
Daily Change_New =
VAR CurrentDate = ActualsDaily[as_of_date]
VAR PreDate =
CALCULATE (
MAX ( ActualsDaily[as_of_date] ),
FILTER (
ActualsDaily,
ActualsDaily[customer] = EARLIER ( ActualsDaily[customer] )
&& ActualsDaily[facility_customer] = EARLIER ( ActualsDaily[facility_customer] )
&& ActualsDaily[as_of_date] < CurrentDate
)
)
VAR PreDue =
CALCULATE (
SUM ( ActualsDaily[Actual] ),
FILTER (
ActualsDaily,
ActualsDaily[as_of_date] = PreDate
&& ActualsDaily[customer] = EARLIER ( ActualsDaily[customer] )
&& ActualsDaily[facility_customer] = EARLIER ( ActualsDaily[facility_customer] )
)
)
RETURN
(
IF ( PreDue <> BLANK (), ActualsDaily[Actual] - PreDue )
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @sridharpolina
You may try the following calculated column to see if it helps.
Daily Change_New =
VAR CurrentDate = ActualsDaily[as_of_date]
VAR PreDate =
CALCULATE (
MAX ( ActualsDaily[as_of_date] ),
FILTER (
ActualsDaily,
ActualsDaily[customer] = EARLIER ( ActualsDaily[customer] )
&& ActualsDaily[facility_customer] = EARLIER ( ActualsDaily[facility_customer] )
&& ActualsDaily[as_of_date] < CurrentDate
)
)
VAR PreDue =
CALCULATE (
SUM ( ActualsDaily[Actual] ),
FILTER (
ActualsDaily,
ActualsDaily[as_of_date] = PreDate
&& ActualsDaily[customer] = EARLIER ( ActualsDaily[customer] )
&& ActualsDaily[facility_customer] = EARLIER ( ActualsDaily[facility_customer] )
)
)
RETURN
(
IF ( PreDue <> BLANK (), ActualsDaily[Actual] - PreDue )
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What's your reasoning for using LOOKUPVALUE? In your scenario CALCULATE would be much easier.
Daily Change_New =
VAR CurrentDate = ActualsDaily[as_of_date]
VAR PreDate =
CALCULATE (
MAX ( ActualsDaily[as_of_date] ),
ActualsDaily[as_of_date] < CurrentDate
)
VAR PreDue =
CALCULATE (
SUM(ActualsDaily[Actual]),
ActualsDaily[as_of_date] = PreDate
)
RETURN
(
IF ( PreDue <> BLANK (), ActualsDaily[Actual] - PreDue )
)
Adjust as needed for your other filters.
Covering 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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |