Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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
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
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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.
@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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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.
@Joorge_C Could you share your fact table and dimension table? and an example of what the expected result would be?
Thanks
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 |
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
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!
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 )
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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?
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
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 )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |