Running sum for last 3 Values in a Non Continuous Date Table

[Joorge_C]

Hello All,

I have been traying to get a running sum for last 3 values in a non continous date table and I have not been able to achieve this.

My main data is only Month related so there is No Date Values. It seems the ISONORAFTER does not work in non continous dates values.

I want to add the Days in a Month

I have tried these 2 and I cannot get the value.

Cal Day x3 B = CALCULATE(SUM('Dim DateCal'[Days in Month]),FILTER(ALL('Dim DateCal'),'Dim DateCal'[FP Number]<=MAX('Dim DateCal'[FP Number]) && 'Dim DateCal'[FP Number]>= MAX('Dim DateCal'[FP Number])-2))

CD Test 2 = CALCULATE(SUM('Dim DateCal'[Days in Month]), DATESINPERIOD('Dim DateCal'[DATE],LASTDATE('Dim DateCal'[DATE]),-3,MONTH))

This is my table:

Month NameYearMonthDays in MonthFP NumberMonth UpsKeyDATE

October	2017	10	31	1	OCT	2017OCT	10/1/2017
November	2017	11	30	2	NOV	2017NOV	11/1/2017
December	2017	12	31	3	DEC	2017DEC	12/1/2017
January	2018	1	31	4	JAN	2018JAN	1/1/2018
February	2018	2	28	5	FEB	2018FEB	2/1/2018
March	2018	3	31	6	MAR	2018MAR	3/1/2018
April	2018	4	30	7	APR	2018APR	4/1/2018
May	2018	5	31	8	MAY	2018MAY	5/1/2018
June	2018	6	30	9	JUN	2018JUN	6/1/2018
July	2018	7	31	10	JUL	2018JUL	7/1/2018
August	2018	8	31	11	AUG	2018AUG	8/1/2018
September	2018	9	30	12	SEP	2018SEP	9/1/2018
October	2018	10	31	1	OCT	2018OCT	10/1/2018
November	2018	11	30	2	NOV	2018NOV	11/1/2018
December	2018	12	31	3	DEC	2018DEC	12/1/2018
January	2019	1	31	4	JAN	2019JAN	1/1/2019
February	2019	2	28	5	FEB	2019FEB	2/1/2019
March	2019	3	31	6	MAR	2019MAR	3/1/2019
April	2019	4	30	7	APR	2019APR	4/1/2019
May	2019	5	31	8	MAY	2019MAY	5/1/2019
June	2019	6	30	9	JUN	2019JUN	6/1/2019
July	2019	7	31	10	JUL	2019JUL	7/1/2019
August	2019	8	31	11	AUG	2019AUG	8/1/2019
September	2019	9	30	12	SEP	2019SEP	9/1/2019

[LivioLanzo]

If you modify your model and convert it into a real Date Table then you will be able to use TimeIntelligence functions without a problem. To do this you can generate a new table (which you will mark as Date table) like this:

=
GENERATE (
    'Dim DateCal',
    VAR Dte = 'Dim DateCal'[Date]
    VAR DaysInMonth = 'Dim DateCal'[Days in Month]
    RETURN
        GENERATESERIES (
            Dte,
            Dte + DaysInMonth - 1,
            1
        )
)

[Joorge_C]

Thanks Livio,

Unfortunately, my raw data only includes Month, and with no dates informaiton, so I created the Date Table Non continous to be able to link this to another dataset, and if I add a Date by Day, I do not have a Sigle Month to be able to Link to in relatioship with Both tables as it will contain multiple values.

I tried making a connector with a single table in between a Real Date Table and my data with just the Key values in Month/year, but this also screwed up the Time and didnt work.

[LivioLanzo]

@Joorge_C  you could turn your Month column within your fact table to a real date (first day of the month) then you can build the relationship with the date column within the Dimension Date table

[Joorge_C]

Thanks Livio, just tried this but it continues to give weird values when doing the cumulative of workdays for the past 3 months data. Not sure if somewhere with the data, there is an issue with the one day to a month relationship.

[LivioLanzo]

@Joorge_C Could you share your fact table and dimension table? and an example of what the expected result would be?

Thanks

[Joorge_C]

Sure! Thanks in advance for the help

Im tryin got get a Running Sum of the days in the Month and to break every 3 months back.. In other words sum the last 3 months calendar days. Example below.

Download here: Dim Cal TEST

	-Oct-	-Nov-	-Dec-	-Jan-	-Feb-	-Mar-
Calendar Days Per Month	31	30	31	31	28	31
Messure	31	61	92	92	90	90

[v-juanli-msft · Accepted solution]

Hi @Joorge_C

Asumme your original table like this

First, i create a calendar date table

Click on Home->New table, type this formula

calendar = CALENDAR(DATE(2017,10,1),DATE(2019,9,30))

Then create calculated columns

year = YEAR([Date]) 

month = MONTH([Date])

year-month = CONCATENATE(CONCATENATE([year],"-"),[month])

days in month = CALCULATE(COUNT('calendar'[Date]),ALLEXCEPT('calendar','calendar'[year-month])) 

Second, go to Queries editor, add a conditional column ("month number") in your original table,

Code in Advanced editor

#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Month] = "October" then 10 else if [Month] = "November" then 11 else if [Month] = "December" then 12 else if [Month] = "January" then 1 else if [Month] = "February" then 2 else if [Month] = "March" then 3 else if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else if [Month] = "July" then 7 else if [Month] = "August" then 8 else if [Month] = "September" then 9 else null)

Then create a calculated column [key] in your original table, with this column, i could manage the relationship between "original table" and "calendar" table based on "original table" [key] and "calendar"[year-month] 

key = CONCATENATE(CONCATENATE([Year],"-"),[month number])

Then create calculated columns in the table "original table" 

days = LOOKUPVALUE('calendar'[days in month],'calendar'[year-month],[key])

firstdate = CALCULATE(MIN('calendar'[Date]),ALLEXCEPT('opriginal table','opriginal table'[key]))

new month number = SWITCH(TRUE(),[Month]="January",4,[Month]="February",5,[Month]="March",6,
[Month]="April",7,[Month]="May",8,[Month]="June",9,[Month]="July",10,[Month]="August",11,
[Month]="September",12,[Month]="October",1,[Month]="November",2,[Month]="December",3)

Finally create a measure

Measure = CALCULATE(SUM('opriginal table'[days]),FILTER(ALL('opriginal table'),
[firstdate]<=MAX([firstdate])&&[new month number]>=MAX([new month number])-2))

Best Regards

maggie

[Joorge_C]

Hi Maggie, thanks a lot for your detailed steps.

I have been able to make this progress in my initial pbix file.

Are you able to filter a single year and have the calculated metric start off with 31 for Oct?

Example, filter 2018 only, and see if the calculated column starts with 31 or if its summing up 3 montsh before and has 90+.

Thanks again!

[v-juanli-msft]

Hi @Joorge_C

Yes, i can.

Before filter

after filter

Best Regards

Maggie

[LivioLanzo]

Hi @Joorge_C

after building the calendar table as I showed in my first reply and marking this new table as a date table with the field VALUE as the datekey, this is how I built your calculation:

Last 3 Months =
CALCULATE (
    COUNTROWS ( Dates ),
    DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -3, MONTH ),
    ALL ( Dates )
)

[Joorge_C]

Livio, Thanks a lot for this, Im able to see now the running sum.

One last tricky question I have ... Do you know how to sort this by Fiscal Period, with the staring month as October and Not January?

Thanks again!

Month Name Last 3 Months B
October 92
September 92
August 92
July 92
June 91
May 92
April 89
March 90
February 59
January 31
December 92
November 91

[LivioLanzo]

You need to create a whole integer column in your table by which you will sort Fiscal Period using the Sort By Column button on the Modelling tab

[Joorge_C]

Thanks Livio, I have already done this, only thing is the Time intelligcen formula on the Running Last 3 Months, starts summing on January and Not October (results posted in previous post). Any other Ideas?

[v-juanli-msft]

Hi @Joorge_C

When clicking on the link access to your data, it needs an organizational account which i don't have, so i am unable to download your data.

To share your file, you may send it via private message or upload to OneDrive and share the link with me.

Here is my test pbix

Best Regards

Maggie

[LivioLanzo]

if it is correct inmy screenshot, I can share with you the pbix file later on. 

The DatesinPeriod is not affected by the when the fiscal year starts (Unless the notion of period changes, like in 4-4-5 calendars which it is not the case for you )