topic Re: rolling totals from secondary date in DAX Commands and Tips

rolling totals from secondary date

luzsoulez — Fri, 28 Jul 2023 12:17:01 GMT

In my table I have 2 dates, start date which is related to the date table and end date which is not related to the date table

I made with no problems the rolling total for start date revenue, but I need to subtract the rolling total of the ends to get the net revenue for the period. I am having problems with the second part given that end date is not related to the date table.

Running actuals = var Maxdate= MAX(Date_Master[Date]) return CALCULATE([Sum Actuals],Date_Master[Date]<= Maxdate, ALL(Date_Master[Date]))- [Running actuals Ends]

Running actuals Ends = var Maxdate= MAX(Date_Master[Date]) return CALCULATE([Sum Actuals],'Consolidated Master'[Calc End Date]< Maxdate, ALL(Actuals[Calc End Date]))

Re: rolling totals from secondary date

eliasayyy — Fri, 28 Jul 2023 12:46:23 GMT

can you please provide sample data to better understand

Re: rolling totals from secondary date

eliasayyy — Fri, 28 Jul 2023 13:44:14 GMT

do you have calendar table? and what is master date? and cant you copy the table instead of picture to insert it directly to powerbi ?

Re: rolling totals from secondary date

luzsoulez — Fri, 28 Jul 2023 14:40:20 GMT

I use the standard DAX date table produced by RADACAD and the relations ship is 'Date Master'[date] to 'mydata'[Calc Start date]

Item	Employee Name	Official Start Date	Calc Start date	End Date	Status	Division	Delivery Center	Total Starts	Calc End Date
Actuals	Random1	16-Jan-23	16-Jan-23		Active	Div 1	country 1	1	16-Jan-24
Actuals	Random1	3-Apr-23	3-Apr-23		Active	Div 2	country 2	1	1-Aug-23
Actuals	Random1	4-Aug-22	4-Aug-22		Active	Div 1	country 3	1	23-Apr-23
Actuals	Random1	7-Nov-22	7-Nov-22		Active	Div 2	country 1	1	31-Jul-23
Actuals	Random1	7-Feb-22	7-Feb-22		Active	Div 1	country 2	1	29-Dec-23
Actuals	Random1	27-Dec-22	27-Dec-22		Active	Div 2	country 3	1	27-Dec-23
Actuals	Random1	16-Jan-23	16-Jan-23		Active	Div 1	country 1	1	16-Jan-24
Actuals	Random1	16-Jan-23	16-Jan-23		Active	Div 2	country 2	1	16-Jan-24
Actuals	Random1	4-Apr-23	4-Apr-23		Active	Div 1	country 3	1	30-Sep-23
Actuals	Random1	7-Sep-21	7-Sep-21		Active	Div 2	country 1	1	2-Oct-23
Actuals	Random1	27-Dec-22	27-Dec-22		Active	Div 1	country 2	1	27-Dec-23
Actuals	Random1	27-Dec-22	27-Dec-22		Active	Div 2	country 3	1	27-Dec-23
Actuals	Random1	14-Nov-22	14-Nov-22		Active	Div 1	country 1	1	31-Jul-23
Actuals	Random1	16-Jan-23	16-Jan-23		Active	Div 2	country 2	1	16-Jan-24
Target			1-Dec-23	31-Dec-23		Div 1	country 3	4	1-Jan-24
Target			1-Nov-23	31-Dec-23		Div 2	country 1	4	1-Jan-24
Target			1-Oct-23	31-Dec-23		Div 1	country 2	4	1-Jan-24
Target			1-Sep-23	31-Dec-23		Div 2	country 3	4	1-Jan-24
Target			1-Aug-23	31-Dec-23		Div 1	country 1	3	1-Jan-24
Target			1-Jul-23	31-Dec-23		Div 2	country 2	2	1-Jan-24
Target			1-Jun-23	31-Dec-23		Div 1	country 3	2	1-Jan-24
Target			1-May-23	31-Dec-23		Div 2	country 1	1	1-Jan-24
Target			1-Apr-23	31-Dec-23		Div 1	country 2	1	1-Jan-24
Target			1-Dec-23	31-Dec-23		Div 2	country 3	4	1-Jan-24
Target			1-Nov-23	31-Dec-23		Div 1	country 1	4	1-Jan-24
Target			1-Oct-23	31-Dec-23		Div 2	country 2	4	1-Jan-24
Target			1-Sep-23	31-Dec-23		Div 1	country 3	4	1-Jan-24
Target			1-Aug-23	31-Dec-23		Div 2	country 1	3	1-Jan-24
Target			1-Jul-23	31-Dec-23		Div 1	country 2	2	1-Jan-24
Target			1-Jun-23	31-Dec-23		Div 2	country 3	2	1-Jan-24
Target			1-May-23	31-Dec-23		Div 1	country 1	1	1-Jan-24