Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to get the running total value on the rows of the future date till the end of the year.
I have calculated my Running total like this:
Amount to be Delivered Running Total(late) =
CALCULATE(
SUM('Month overview'[Amount To Be Delivered (€)]),
FILTER(ALLSELECTED('Month overview'[DLV / DLV ZRP Date]),
'Month overview'[DLV / DLV ZRP Date]<=MAX('Month overview'[DLV / DLV ZRP Date])
&& 'Month overview'[DLV / DLV ZRP Date]<= TODAY()
))
and I use a DimTable
Solved! Go to Solution.
@Kpham
What is the result of your current measure, my concern is the filter condition: " 'Month overview'[DLV / DLV ZRP Date]<=MAX('Month overview'[DLV / DLV ZRP Date])", since you want it yearly.
Try change it to the following measure:
Amount to be Delivered Running Total(late) =
CALCULATE(
SUM('Month overview'[Amount To Be Delivered (€)]),
FILTER(ALLSELECTED('Month overview'[DLV / DLV ZRP Date]),
'Month overview'[DLV / DLV ZRP Date].[Year]=MAX('Month overview'[DLV / DLV ZRP Date].[Year])
&& 'Month overview'[DLV / DLV ZRP Date]<= TODAY()
))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Kpham
What is the result of your current measure, my concern is the filter condition: " 'Month overview'[DLV / DLV ZRP Date]<=MAX('Month overview'[DLV / DLV ZRP Date])", since you want it yearly.
Try change it to the following measure:
Amount to be Delivered Running Total(late) =
CALCULATE(
SUM('Month overview'[Amount To Be Delivered (€)]),
FILTER(ALLSELECTED('Month overview'[DLV / DLV ZRP Date]),
'Month overview'[DLV / DLV ZRP Date].[Year]=MAX('Month overview'[DLV / DLV ZRP Date].[Year])
&& 'Month overview'[DLV / DLV ZRP Date]<= TODAY()
))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Kpham , if you need only at year level
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]<=max('Date'[Year])))
Try with dates, example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com