Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Region | Plan_Date | Target | Cummulative Total |
East | 01/05/2021 | 0 | |
East | 02/05/2021 | 0 | |
East | 03/05/2021 | 20 | |
East | 04/05/2021 | 25 | |
East | 05/05/2021 | 30 | |
East | 06/05/2021 | 35 | |
East | 07/05/2021 | 40 | |
East | 08/05/2021 | 0 | |
East | 09/05/2021 | 0 | |
East | 10/05/2021 | 55 | |
East | 11/05/2021 | 60 | |
West | 01/05/2021 | 0 | |
West | 02/05/2021 | 0 | |
West | 03/05/2021 | 75 | |
West | 04/05/2021 | 80 | |
West | 05/05/2021 | 85 | |
West | 06/05/2021 | 90 | |
West | 07/05/2021 | 95 | |
West | 08/05/2021 | 0 | |
West | 09/05/2021 | 0 | |
West | 10/05/2021 | 110 | |
West | 11/05/2021 | 115 |
Expected Output :-
Region | Plan_Date | Target | Cummulative Total |
East | 01/05/2021 | 0 | 0 |
East | 02/05/2021 | 0 | 0 |
East | 03/05/2021 | 20 | 20 |
East | 04/05/2021 | 25 | 45 |
East | 05/05/2021 | 30 | 75 |
East | 06/05/2021 | 35 | 110 |
East | 07/05/2021 | 40 | 150 |
East | 08/05/2021 | 0 | 150 |
East | 09/05/2021 | 0 | 150 |
East | 10/05/2021 | 55 | 205 |
East | 11/05/2021 | 60 | 265 |
West | 01/05/2021 | 0 | 0 |
West | 02/05/2021 | 0 | 0 |
West | 03/05/2021 | 75 | 75 |
West | 04/05/2021 | 80 | 155 |
West | 05/05/2021 | 85 | 240 |
West | 06/05/2021 | 90 | 330 |
West | 07/05/2021 | 95 | 425 |
West | 08/05/2021 | 0 | 425 |
West | 09/05/2021 | 0 | 425 |
West | 10/05/2021 | 110 | 535 |
West | 11/05/2021 | 115 | 650 |
North | 01/05/2021 | 0 | 0 |
North | 02/05/2021 | 0 | 0 |
North | 03/05/2021 | 130 | 130 |
North | 04/05/2021 | 135 | 265 |
North | 05/05/2021 | 140 | 405 |
North | 06/05/2021 | 145 | 550 |
North | 07/05/2021 | 150 | 700 |
North | 08/05/2021 | 0 | 700 |
North | 09/05/2021 | 0 | 700 |
North | 10/05/2021 | 165 | 865 |
North | 11/05/2021 | 170 | 1035 |
South | 01/05/2021 | 0 | 0 |
South | 02/05/2021 | 0 | 0 |
South | 03/05/2021 | 185 | 185 |
South | 04/05/2021 | 190 | 375 |
South | 05/05/2021 | 195 | 570 |
South | 06/05/2021 | 200 | 770 |
South | 07/05/2021 | 205 | 975 |
South | 08/05/2021 | 0 | 975 |
South | 09/05/2021 | 0 | 975 |
South | 10/05/2021 | 220 | 1195 |
South | 11/05/2021 | 225 | 1420 |
Solved! Go to Solution.
ok try this
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @balajir98 ,
here is my solution:
Running Total =
CALCULATE (
SUM ( 'Table'[Target] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Region] ),
'Table'[Plan_Date] <= MAX ( 'Table'[Plan_Date])
)
)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi,
You may download my PBI file from here.
Hope this helps.
@balajir98 , Try
calculate(sum(Table[Target]), filter(Table, [Region] = earlier([Region]) && [Plan_Date] <= max(Table[Plan_Date])))
Thank you for the reply amit, but i am getting the total value for all the rows, please find the below photos,
Hi @balajir98
here is the formula
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thank you jer bi, but this is not my expected output, cummulated we should add the earlier value with 0, i have attached the image for your reference, Advance thanks
Change Sheet1 by the name of your table
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thank you Jerbi, now East region came perfect, but for west region the cummulative totals are mismatch, West March 5th Vale is 75 but in cummulative total it is showing as 95 and the missmatch follows, Thank you in advance.🙂
Yes myy bad sorry,
Just delete the "<" before _region
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thank you jerbi, i tried with your code, but i am getting blank, please find the below screenshot
Indeed, it's a measure for a Table visual. It doesn't work for an added column.
Why do you need to add a column to your model? it takes space and performance, and we add Columns in order to use it as a slicer when necessary.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Ok jerbi, understood, i have in table also, though it is not workings, please find the below screenshot
ok try this
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thank you so much jerbi, it is working now perfect, thank you again for your help, 😊😊😊🙏
You are welcome, eventhough i don't recommend adding new columns into your model espcially when you are not using it as a slicer.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |