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.
Hi guys,
Below is the link for a sample data set where I'm trying to create a running total for actuals until current week where as I cannot seem to find a way to achieve this. The earlier posts have a date field to have this achieved but I have a column with week & year column to have this achieved, not sure how to have this achieved?
Please help.
https://1drv.ms/u/s!Aj7kf6Fnqc5SwwbUodJXttTxGA-A
Regards,
A!
Maybe this can help in any way:
1. Goto Edit Queries and sort by date
2. Create in index column
3. Create a calculated column by:
Cumulative = CALCULATE(SUM(Data[Actual]);FILTER(Data;Data[Index]<EARLIER(Data[Index])-1))
But I see you have many rows without dates which makes me confused.
Hi @Johanno,
Thank you for the below alternative but this one gives the 3.5million as actuals in value which should not be the case and I think this is because of the number of blanks i have in my dataset. The sample I shared is subset of data 😞
Regards,
A!
Ok, I don't fully understand. The sum of all actuals should be the last figure in the cumulative column.
Hi @Johanno,
I want a cumulative value of actuals until the current week. However, the calculation provides the overall total value but does not breakdown per week value when plotted in line visual. I used the below mentioned measure to list me the values per week but gives me the actuals until the max value of date but I want the calculation to stop for the current week.
Measure: Actuals show up for the entire time period when I use the below calculation where column [Yr | Wk - DueDate] is a combination of year and week #
CALCULATE(
SUM('tblEBV'[ACTUAL BV]),
FILTER(
ALLSELECTED('tblEBV'[Yr | Wk - DueDate]),
ISONORAFTER('tblEBV'[Yr | Wk - DueDate], MIN('tblEBV'[Yr | Wk - DueDate]), DESC)
)
)
Kind regards,
A!
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi @Greg_Deckler,
Thank you for sharing the link, I had already made use of the logic earlier in my dataset but does not seem to work for me as I have a derivative column for year and week based on date column to plot in my line diagram and the actuals flow beyond the current week which I want to restrict 😞
Kind regards,
A!
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |