Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpt1228
Responsive Resident
Responsive Resident

DAX for 4 Week Fiscal Periods - % CHG VS Previous Period

Hello, I think one of the most aggrivating issues with Power BI is when you have custom fiscal periods and the associated time based calulcations that don't work as the built in time periods of Month, Quarter, Year.

 

I have 13 fiscal periods which are in my date table as =Roundup(ISOweek/4). I need to bring a measure in that will show me the % chg from the previous fiscal period. I would also want to filter on the last completed fiscal period and compare the last completed VS the previous completed fiscal period.

 

 VS previous period.JPG

 

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@jpt1228 - Try the following Calculated Column:

 

Relative Fiscal Period = 
VAR PeriodKey = DimDate[Year] * 100 + DimDate[Fiscal Period]
VAR SummarizePeriods = SUMMARIZE(DimDate, DimDate[Fiscal Period], DimDate[Year], "PeriodKey",DimDate[Year]*100+DimDate[Fiscal Period])
RETURN COUNTROWS(FILTER(SummarizePeriods, [PeriodKey] <= PeriodKey))

Cheers!

Nathan

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

@jpt1228  - The simplest solution: add "Relative Period" columns to your date table. So, you can set the last completed 4 week period to 0, the prior period to -1, etc. 

Then, in your DAX, you can calculate the previous period like this:

Last Period Measure = 
relperiod = MAX(Date[Relative Period])
return CALCULATE([your measure], Date[Relative Period] = relperiod - 1)

Hope this helps,

Nathan

jpt1228
Responsive Resident
Responsive Resident

Hello @Anonymous  I do have a column in the date table that identifies IsCurrentPeriod. Are you saying to create another column that would be IsCurrentPeriod - 1? I suppose this would get messy at the beginning of the next year fiscal period.

 

How do I add relative period column?

 

Thanks

Anonymous
Not applicable

@jpt1228 - Here is an M script for date table, that contains relative days, weeks, months, quarters, and years. So today's relative day is 0, yesterday = -1, one year ago = -365, etc. You will need to do something similar for your 4 week period.

 

let
    //Set the following variables
    Culture = "English (United States)", //Select a culture.
    UseYesterdayAsCurrentDate = true, //true = yesterday, false = today
    YearsBack = 2, //How many years to include prior to the current year.
    YearsAhead = 2, //How many years to include after the current year.
    GoToBeginning = "Year", //Options: Year, Month, None
    GoToEnd = "Year", //Options: Year, Month, None

    //Figure the Start and End Dates, based on above variables
    DateToday = DateTime.Date(DateTime.LocalNow()),
    CurrentDate = if UseYesterdayAsCurrentDate = true then Date.AddDays(DateToday, -1) else DateToday,
    YearBegin = Date.Year(CurrentDate) - YearsBack,
    MonthBegin = if GoToBeginning = "Year" then 1 else Date.Month(CurrentDate),
    DayBegin = if GoToBeginning = "None" then Date.Day(CurrentDate) else 1,
    StartDate = #date(YearBegin, MonthBegin, DayBegin), 
    YearEnd = Date.Year(CurrentDate) + YearsAhead,
    MonthEnd = if GoToEnd = "Year" then 12 else Date.Month(CurrentDate),
    DayEndTemp = if GoToEnd = "Year" then 31 else Date.Day(CurrentDate),
    EndDateTemp = #date(YearEnd, MonthEnd, DayEndTemp), 
    EndDate = if GoToEnd = "Month" then DateTime.Date(Date.EndOfMonth(EndDateTemp)) else EndDateTemp,
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,

    //Get complete list of dates, Convert to a table, update name and data type
    AllDates = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(AllDates, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),

    //Add other attributes of the date, as desired
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarterOfYear = Table.AddColumn(InsertYear, "Quarter Of Year", each Date.QuarterOfYear([Date])),
    InsertMonthOfYear = Table.AddColumn(InsertQuarterOfYear, "Month Of Year", each Date.Month([Date])),
    InsertDayOfMonth = Table.AddColumn(InsertMonthOfYear, "Day Of Month", each Date.Day([Date])),
    InsertDayOfWeek = Table.AddColumn(InsertDayOfMonth, "Day Of Week", each Date.DayOfWeek([Date])),
    InsertDateAlternateKey = Table.AddColumn(InsertDayOfWeek, "Date Alternate Key", each [Year] * 10000 + [Month Of Year] * 100 + [Day Of Month]),
    InsertMonthName = Table.AddColumn(InsertDateAlternateKey, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertMonthKey = Table.AddColumn(InsertMonthName, "Month Key", each [Year] * 100 + [Month Of Year]),
    InsertMonthYear = Table.AddColumn(InsertMonthKey, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])),
    InsertQuarterName = Table.AddColumn(InsertMonthYear, "Quarter Name", each "Q" & Number.ToText([Quarter Of Year])),
    InsertQuarterKey = Table.AddColumn(InsertQuarterName, "Quarter Key", each [Year] * 100 + [Quarter Of Year]),
    InsertQuarterYear = Table.AddColumn(InsertQuarterKey, "Quarter Year", each "Q" & Number.ToText([Quarter Of Year]) & " " & Number.ToText([Year])),
    InsertDayName = Table.AddColumn(InsertQuarterYear, "Day Of Week Name", each Date.ToText([Date], "dddd", Culture), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date),

    //Add Relative Date Positions.
    InsertRelativeYear = Table.AddColumn(InsertWeekEnding, "Relative Year", each [Year]-Date.Year(CurrentDate)),
    InsertRelativeYearDescription = Table.AddColumn(InsertRelativeYear, "Relative Year Description", 
                                    each if [Relative Year] = 0 then "Current Year"
                                        else if [Relative Year] = -1 then "Last Year"
                                        else if [Relative Year] = 1 then "Next Year"
                                        else if [Relative Year] < -1 then Number.ToText(Number.Abs([Relative Year])) & " Years Back" 
                                        else Number.ToText([Relative Year]) & " Years Ahead"),
    InsertRelativeQuarter = Table.AddColumn(InsertRelativeYearDescription, "Relative Quarter", 
                                    each 4*([Year]-Date.Year(CurrentDate)) + ([Quarter Of Year]-Date.QuarterOfYear(CurrentDate))),
    InsertRelativeQuarterDescription = Table.AddColumn(InsertRelativeQuarter, "Relative Quarter Description", 
                                    each if [Relative Quarter] = 0 then "Current Quarter"
                                        else if [Relative Quarter] = -1 then "Last Quarter"
                                        else if [Relative Quarter] = 1 then "Next Quarter"
                                        else if [Relative Quarter] < -1 then Number.ToText(Number.Abs([Relative Quarter])) & " Quarters Back" 
                                        else Number.ToText([Relative Quarter]) & " Quarters Ahead"),
    InsertRelativeMonth = Table.AddColumn(InsertRelativeQuarterDescription, "Relative Month", 
                                    each 12*([Year]-Date.Year(CurrentDate)) + ([Month Of Year]-Date.Month(CurrentDate))),
    InsertRelativeMonthDescription = Table.AddColumn(InsertRelativeMonth, "Relative Month Description", 
                                    each if [Relative Month] = 0 then "Current Month"
                                        else if [Relative Month] = -1 then "Last Month"
                                        else if [Relative Month] = 1 then "Next Month"
                                        else if [Relative Month] < -1 then Number.ToText(Number.Abs([Relative Month])) & " Months Back" 
                                        else Number.ToText([Relative Month]) & " Months Ahead"),
    InsertRelativeWeek = Table.AddColumn(InsertRelativeMonthDescription, "Relative Week", 
                                    each Duration.Days(Duration.From([Week Ending]-Date.EndOfWeek(CurrentDate)))/7),
    InsertRelativeWeekDescription = Table.AddColumn(InsertRelativeWeek, "Relative Week Description", 
                                    each if [Relative Week] = 0 then "Current Week"
                                        else if [Relative Week] = -1 then "Last Week"
                                        else if [Relative Week] = 1 then "Next Week"
                                        else if [Relative Week] < -1 then Number.ToText(Number.Abs([Relative Week])) & " Weeks Back" 
                                        else Number.ToText([Relative Week]) & " Weeks Ahead"),
    InsertRelativeDay = Table.AddColumn(InsertRelativeWeekDescription, "Relative Day", 
                                    each Duration.Days(Duration.From([Date]-CurrentDate))),
    InsertRelativeDayDescription = Table.AddColumn(InsertRelativeDay, "Relative Day Description", 
                                    each if [Relative Day] = 0 then "Current Day"
                                        else if [Relative Day] = -1 then "Last Day"
                                        else if [Relative Day] = 1 then "Next Day"
                                        else if [Relative Day] < -1 then Number.ToText(Number.Abs([Relative Day])) & " Days Back" 
                                        else Number.ToText([Relative Day]) & " Days Ahead"),

    //Add Date Category Positions.
    InsertYearGroup = Table.AddColumn(InsertRelativeDayDescription, "Year Group", 
                                    each if [Relative Year] = 0 then "Current Year"
                                        else if [Relative Year] < 0 then "Past Years"
                                        else "Future Years"),
    InsertQuarterGroup = Table.AddColumn(InsertYearGroup, "Quarter Group", 
                                    each if [Relative Quarter] = 0 then "Current Quarter"
                                        else if [Relative Quarter] < 0 then "Past Quarters"
                                        else "Future Quarters"),
    InsertMonthGroup = Table.AddColumn(InsertQuarterGroup, "Month Group", 
                                    each if [Relative Month] = 0 then "Current Month"
                                        else if [Relative Month] < 0 then "Past Months"
                                        else "Future Months"),
    InsertWeekGroup = Table.AddColumn(InsertMonthGroup, "Week Group", 
                                    each if [Relative Week] = 0 then "Current Week"
                                        else if [Relative Week] < 0 then "Past Weeks"
                                        else "Future Weeks"),
    InsertDayGroup = Table.AddColumn(InsertWeekGroup, "Day Group", 
                                    each if [Relative Day] = 0 then "Current Day"
                                        else if [Relative Day] < 0 then "Past Days"
                                        else "Future Days"),
    #"Changed Type" = Table.TransformColumnTypes(InsertDayGroup,{{"Year", Int64.Type}, {"Quarter Of Year", Int64.Type}, {"Month Of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day Of Week", Int64.Type}, {"Date Alternate Key", Int64.Type}, {"Quarter Name", type text}, {"Month Year", type text}, {"Quarter Year", type text}, {"Relative Year Description", type text}, {"Relative Quarter Description", type text}, {"Relative Month Description", type text}, {"Relative Week Description", type text}, {"Relative Day Description", type text}, {"Year Group", type text}, {"Quarter Group", type text}, {"Month Group", type text}, {"Week Group", type text}, {"Day Group", type text}, {"Relative Day", Int64.Type}, {"Relative Week", Int64.Type}, {"Relative Month", Int64.Type}, {"Relative Quarter", Int64.Type}, {"Relative Year", Int64.Type}, {"Quarter Key", Int64.Type}, {"Month Key", Int64.Type}}),

    //Add Date In Fact Table Attributes.
    maxSales = Date.AddDays(DateToday, -1), //List.Max(Table.Column(Sales, "SalesDate")), //
    InsertHasSalesData = Table.AddColumn(#"Changed Type", "Has Sales Data", 
                                    each if [Date] <= maxSales  then "Yes"
                                        else "No"),
    InsertHasSalesDataCompleteMonth = Table.AddColumn(InsertHasSalesData, "Has Sales Data Complete Month", 
                                    each if Date.EndOfMonth([Date]) <= maxSales then "Yes"
                                        else "No")
in
    InsertHasSalesDataCompleteMonth

 

 

jpt1228
Responsive Resident
Responsive Resident

Hi @Anonymous  I'm not really sure what to do with this. I already have a date table. This seems a bit more complex. Are you showing this as an example? Was hoping for a more simple CurrentPeriod - 1, Current Period -2. I know not dax but general idea. I have a column that indicates current period true/false.

Anonymous
Not applicable

@jpt1228 -

Yes, it's an example, intended to show the logic behind the relative periods.

Please provide code for your date table, including the "current period" calc. I think that whatever logic you use to calculate "current period" can be extended to "relative periods".

jpt1228
Responsive Resident
Responsive Resident

My date table was one of the first that I created when building models so I didn't build it with code (unfortunately).

 

Here are the columns I created in the DimDate. The calcs for the fiscal period are:

 

Fiscal Period = ROUNDUP( DimDate[ISO Week in Year]/4,0)
 
IsCurrentFiscalPeriod =
VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =
TODAY()), DimDate[Fiscal Period])
RETURN
IF(dimdate[Fiscal Period] = TodaysPeriod, TRUE(), FALSE())

 

 

Anonymous
Not applicable

@jpt1228 - Try the following Calculated Column:

 

Relative Fiscal Period = 
VAR PeriodKey = DimDate[Year] * 100 + DimDate[Fiscal Period]
VAR SummarizePeriods = SUMMARIZE(DimDate, DimDate[Fiscal Period], DimDate[Year], "PeriodKey",DimDate[Year]*100+DimDate[Fiscal Period])
RETURN COUNTROWS(FILTER(SummarizePeriods, [PeriodKey] <= PeriodKey))

Cheers!

Nathan

jpt1228
Responsive Resident
Responsive Resident

Thanks @Anonymous  I created that column, however the column returns 94. I am assuming this should "index" based on the year + fiscal period number but it is not.

 

RelativeFisc.JPG

Anonymous
Not applicable

@jpt1228 - It is working in my sample here. Could you share your date table in a pbix?

jpt1228
Responsive Resident
Responsive Resident

Hello @Anonymous  Looks like I had a typo in the formula. Now it is working. I will go back to your original formula and see if I can get it working.

 

Anonymous
Not applicable

I should clarify: this isn't actualy relative period, but "period key", which will work across years. So, it can compare any period to the period prior to it.

The only potential issue will be how you define the 4 week periods at the beginning / end of the year.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.