Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I wonder to know to create this "inverse" cumulative sum.
As a reference, I already took this link already, it works...
But instead of start with the value of the first period, I want to start from the total and subtracting the value of the current period until cero
Date Value "Inverse"Cumulative "Normal"Cumulative
Day0 0 12 0
Day1 2 10 2
Day2 4 6 6
Day3 6 0 12
Thanks in advance
Solved! Go to Solution.
Hi @CarlosR
It was challenging to get a solution for this.
I am assuming you have a calendar / Date table based on the Date of your FactTable and the two are linked.
If you do so, please follow the steps
1. Create a column
Month_Start_Date = STARTOFMONTH(Calendar[Date]).
For each row it will be FirstDay of the corresponding month.
2. Create a column
MaxDate = Max(Calendar[Date])
This will be last date of your FactTable.
3. Create Month_End_Date = IF ( EOMONTH('Calendar'[Date],0) > 'Calendar'[MaxDate],
'Calendar'[MaxDate],EOMONTH('Calendar'[Date],0) )
This crerates for eachrow the lastday of the corresponding month. If the month does not have all days in a month then it will be lastdate of the Date from the FactTable.
4. Create a column
UptoDate = DateDiff(Calendar[Date],'Calendar'[Month_End_Date],DAY) + 1
For Each row it calculates the number of days beween the date and the Month_End_Date
5. Create a column
GobackDate = Date(year(Calendar[Date]),Month(Calendar[Date]),Calendar[UptoDate])
6. Now the final measure
ReverseTotal =
var BeginDate =MAX(Calendar[Month_Start_Date])
var EndDate = Max(Calendar[GobackDate])
return
sumx( Filter( ALL('Calendar'),Calendar[Date] >= BeginDate && 'Calendar'[Date] <= EndDate ),
[SalesAmount]
)
Here [SaleAmount] is a measure of total of Sales from the FactTable.
7. Here is the output for Jan2015 where I have FactTable Records for the wholeMonth 1- 31 Jan 2015
8. Here is the output for Feb2015 where the data is only upto 10 Feb 2015
Try it out it should work wonders for you.
If it works please accpet this as solution and also give KUDOS.
Cheers
CheenuSing
Hi @CarlosR
It was challenging to get a solution for this.
I am assuming you have a calendar / Date table based on the Date of your FactTable and the two are linked.
If you do so, please follow the steps
1. Create a column
Month_Start_Date = STARTOFMONTH(Calendar[Date]).
For each row it will be FirstDay of the corresponding month.
2. Create a column
MaxDate = Max(Calendar[Date])
This will be last date of your FactTable.
3. Create Month_End_Date = IF ( EOMONTH('Calendar'[Date],0) > 'Calendar'[MaxDate],
'Calendar'[MaxDate],EOMONTH('Calendar'[Date],0) )
This crerates for eachrow the lastday of the corresponding month. If the month does not have all days in a month then it will be lastdate of the Date from the FactTable.
4. Create a column
UptoDate = DateDiff(Calendar[Date],'Calendar'[Month_End_Date],DAY) + 1
For Each row it calculates the number of days beween the date and the Month_End_Date
5. Create a column
GobackDate = Date(year(Calendar[Date]),Month(Calendar[Date]),Calendar[UptoDate])
6. Now the final measure
ReverseTotal =
var BeginDate =MAX(Calendar[Month_Start_Date])
var EndDate = Max(Calendar[GobackDate])
return
sumx( Filter( ALL('Calendar'),Calendar[Date] >= BeginDate && 'Calendar'[Date] <= EndDate ),
[SalesAmount]
)
Here [SaleAmount] is a measure of total of Sales from the FactTable.
7. Here is the output for Jan2015 where I have FactTable Records for the wholeMonth 1- 31 Jan 2015
8. Here is the output for Feb2015 where the data is only upto 10 Feb 2015
Try it out it should work wonders for you.
If it works please accpet this as solution and also give KUDOS.
Cheers
CheenuSing
Thanks for your support, now I need to make this column, the MaxDate, specific for a particular dimension, the current solution applies for the complete fact table.
MaxDate = Max(Calendar[Date])
Any idea? thanks
I found it
MaxDate = calCULATE(Max(table[Date]),VALUES(table[Column])
Thanks a lot @CheenuSing it work, need to do some adjustment of course, but it general it worked.
@CheenuSing wrote:Hi @CarlosR
It was challenging to get a solution for this.
I am assuming you have a calendar / Date table based on the Date of your FactTable and the two are linked.
If you do so, please follow the steps
1. Create a column
Month_Start_Date = STARTOFMONTH(Calendar[Date]).
For each row it will be FirstDay of the corresponding month.
2. Create a column
MaxDate = Max(Calendar[Date])
This will be last date of your FactTable.
3. Create Month_End_Date = IF ( EOMONTH('Calendar'[Date],0) > 'Calendar'[MaxDate],
'Calendar'[MaxDate],EOMONTH('Calendar'[Date],0) )
This crerates for eachrow the lastday of the corresponding month. If the month does not have all days in a month then it will be lastdate of the Date from the FactTable.
4. Create a column
UptoDate = DateDiff(Calendar[Date],'Calendar'[Month_End_Date],DAY) + 1
For Each row it calculates the number of days beween the date and the Month_End_Date
5. Create a column
GobackDate = Date(year(Calendar[Date]),Month(Calendar[Date]),Calendar[UptoDate])
6. Now the final measure
ReverseTotal =
var BeginDate =MAX(Calendar[Month_Start_Date])
var EndDate = Max(Calendar[GobackDate])
return
sumx( Filter( ALL('Calendar'),Calendar[Date] >= BeginDate && 'Calendar'[Date] <= EndDate ),[SalesAmount]
)Here [SaleAmount] is a measure of total of Sales from the FactTable.
7. Here is the output for Jan2015 where I have FactTable Records for the wholeMonth 1- 31 Jan 2015
8. Here is the output for Feb2015 where the data is only upto 10 Feb 2015
Try it out it should work wonders for you.
If it works please accpet this as solution and also give KUDOS.
Cheers
CheenuSing
working in the second option...
changing the condition like this should work:
Cumulative 2 = CALCULATE (
SUM ( 'Invoice lines'[Sales] ),
FILTER (
ALLSELECTED(BI-Dates),
'BI-Dates'[Date] >= MIN ( 'BI-Dates'[Date] )
)
)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Tested already, but let me double check, thanks a lot.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |