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
cwayne758
Helper IV
Helper IV

Time Intelligence: TOTALMTD vs DATESMTD vs DATEADD

Hi,

 

I am trying to have a floating measure that tells us the total write-off amounts from the start of the current month to the current day in the current month.

 

I have done this 3 different ways. 2 of my 3 give me the same answer. 

 

Photo attached: 

MTD.PNGmtd1.PNGmtd2.PNG

Thanks in advance 🙂 

1 ACCEPTED SOLUTION

If you want to understand time intelligence better in DAX, read this excellent blog post.

 

In short here is the behavior of the three functions you mentioned.

 

TOTALMTD(): This is only syntactic sugar, all it does is give you the following:

CALCULATE(
    [measure]
    ,DATESMTD(DimDate[Date])
)

DATESMTD(): This works in a date dimension, which must have contiguous, nonrepeating dates from January 1 of the first year you have data to December 31 of the last year you have data. The function returns a 1 column table made up of dates between the first of the month of the current date in context and the current date in context.

 

DATEADD(): This essentially gives you a range of dates (one column table) based on the number of intervals you've requested in either direction. It does not behave intuitively compared to a DATEADD() function in any other language, and I am not aware of any circumstances we (we being a Microsoft BI consultancy with a number of DAX experts) have decided this is the right function to use for any of our work. There is a good description in the linked blog post at the top of my reply.

View solution in original post

16 REPLIES 16
PowerBIGuy
Responsive Resident
Responsive Resident

To use time intelligence you have to use a true date column. the time calculations will not work with a date key. I also find it useful with creating calculations in the context of a month to not only format as date but also use the transform tab to format my column  to equal the end of the month. 

Business Intelligence Architect / Consultant

If you want to understand time intelligence better in DAX, read this excellent blog post.

 

In short here is the behavior of the three functions you mentioned.

 

TOTALMTD(): This is only syntactic sugar, all it does is give you the following:

CALCULATE(
    [measure]
    ,DATESMTD(DimDate[Date])
)

DATESMTD(): This works in a date dimension, which must have contiguous, nonrepeating dates from January 1 of the first year you have data to December 31 of the last year you have data. The function returns a 1 column table made up of dates between the first of the month of the current date in context and the current date in context.

 

DATEADD(): This essentially gives you a range of dates (one column table) based on the number of intervals you've requested in either direction. It does not behave intuitively compared to a DATEADD() function in any other language, and I am not aware of any circumstances we (we being a Microsoft BI consultancy with a number of DAX experts) have decided this is the right function to use for any of our work. There is a good description in the linked blog post at the top of my reply.

HI All,

Realise this topic is marked as solved but I have the same issue. Need to calculate the total for last month.
@greggyb love your link. Heaps of information.

 

 

DATESMTD - I can't use this as my dates table does not continue past the date of the most recent data. (i.e. today)

DATEADD - when using a calculation

Last_Month_TotalKms = CALCULATE([Kms],DATEADD(Dates[Date],-1,month))

I just get the full total for all Kms. The DATEADD filter does not affect the calculation at all. Value does not change when anything in the DATEADD part of the formula is changed. (e.g. if I change it to -5 or the month to day nothing happens)

 

I am wondering if there is another way of approaching the question.

if there is any column calculation to show if in previous month.

e.g. I have a calcuation for if in previous week

IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)

and this feeds

IsInCurrentWeek = if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0)

And

IsInLastWeek = if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0)

So if I could get another column for IsInPreviousMonth that would be great.
Anyone know how to do that?

Cheers

First, is there anything preventing you from loading dates to the end of the current year in your date dimension?

 

Second, Power Query has a lot of built in date functions specifically to create flags like you have mentioned. I'd use those in Power Query rather than defining calculated columns in the data model if I were you. They specifically cover all of the use cases you've laid out, and cover year-end wrapping appropriately.

Hi @greggyb

 

I am using the date table creation from

http://blogs.msdn.com/b/lukaszp/archive/2015/03/05/power-bi-date-filtering.aspx

 

 

//let
//    CreateDateTable = (StartDate, EndDate) =>
let
    StartDate=#date(2012,1,1),
    EndDate=#date(Date.Year(DateTime.LocalNow()),12,31),
    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June"
                 , "July", "August", "September", "October", "November", "December"},
    DayList = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
    //Find the number of days between the end date and the start date
    NumberOfDates = Duration.Days(EndDate-StartDate),
    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

Has been working great but I have only just noticed it seems to push the date out to the 30th of December instead of the 31st.

Strange.

the line

EndDate=#date(Date.Year(DateTime.LocalNow()),12,31),

Should do the work but for some reason isnt going right to the end.

 

I've never really thought much about the quirk of the end date. I'm assuming that it essentially does 365-1 (December 31 - January 1) to get a duration of 364. I just add one to the NumberOfDates in my PQ date script.

 

No real need for MonthList or DayList, as these can be generated in the function Date.ToText( <date>, <format string> 😞

// Power Query M
// Add custom column
MonthName =
Date.ToText( [Date], "mmmm" )

MonthNameShort =
Date.ToText( [Date], "mmm" )

YearMonth =
Date.ToText( [Date], "yyyy - mmm" )

DayName =
Date.ToText( [Date], "dddd" )

DayNameShort =
Date.ToText( [Date], "ddd" )

Hi @greggyb

Thanks for your help with this.

 

My full code for dates is actually

//let
//    CreateDateTable = (StartDate, EndDate) =>
let
    StartDate=#date(2012,1,1),
    EndDate=#date(Date.Year(DateTime.LocalNow()),12,31),
    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June"
                 , "July", "August", "September", "October", "November", "December"},     //changed this day from the original to have Monday first so it would be the first day of the week
    DayList = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
    //Find the number of days between the end date and the start date
    NumberOfDates = Duration.Days(EndDate-StartDate),
    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
    //Turn this list into a table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}
                     , null, ExtraValues.Error),
    //Cast the single column in the table to type date
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
    //Add custom columns for day of month, month number, year
    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),

    //Since Power Query doesn't have functions to return day or month names,
    //use the lists created earlier for this
    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),
    WeekEnding = Table.AddColumn(DayName, "Week Ending", each Date.EndOfWeek([Date])),
    #"Changed Type" = Table.TransformColumnTypes(WeekEnding ,{{"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthYear", each Text.Range([MonthName], 0, 3) & "-" & Number.ToText([Year])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MonthYearNumber", each [Year] * 1000 + [MonthNumberOfYear]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthYearNumber", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Week Ending", "Copy of Week Ending"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Week Ending", "WeekEndingDate"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"WeekEndingDate", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Week Ending", "WeekEnding"}})
in
    #"Renamed Columns1"
//in

//    CreateDateTable

Then add the following columns.

(I guess a lot of these could be added with Power Query automatically but I am still learning and have not got around to getting them in just yet. )

DAX Measures
Today:=DATE(year(now()),MONTH(NOW()), DAY(NOW()))

DAX Calculated Columns IsInCurrentYear =if(YEAR(NOW())= [Year],1,0) WeekOfYearNumber =WEEKNUM([Date],2) IsInCurrentWeek =if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0) IsInCurrentYear = if(YEAR(NOW())= [Year],1,0) // Column to see if it is the current year IsInLastWeek =if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0) IsLast30Days =if(AND([Date]>=[Today]-30,[Date]<=[Today] ),1,0) YearWeekNum = Concatenate(Dates[Year],Dates[WeekOfYearNumber]) WTD = IF(CALCULATE(VALUES(Dates[YearWeekNum]),Dates[Date]=TODAY()-1,ALL(Dates))=Dates[YearWeekNum] && Dates[Date]<=TODAY()-1,"WTD",BLANK()) // shows if it's in the current Week To Date - can use as a filter RelativeDate = [Date]-Today() //shows the difference in days between today and a date // good for looking into the future or so many days back in the past. EOM = EOMONTH(Dates[Date],0) //Add a column that returns true if the date on rows is the current date IsLast7Days = if(AND([Date]>=[Today]-7,[Date]<=[Today]),1,0) // 1 if is in the last 7 days IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date])) //Column to see if it is the day today.

Already have Monthnames, days etc.

 

Looking at the M language I tried adding in the IsInPreviousMonth

 

IsInPreviousMonth = Table.AddColumn(IsInPreviousMonth, "IsInPreviousMonth", each Date.IsInPreviousMonth([Date]))

However it didnt seem to come up? Would have thought this should have just added another column with the info I needed.

 

Final code is

//let
//    CreateDateTable = (StartDate, EndDate) =>
let
    StartDate=#date(2015,1,1),
    EndDate=#date(Date.Year(DateTime.LocalNow()),12,31),
    //Create lists of month and day names for use later on
    MonthList = {"January", "February", "March", "April", "May", "June"
                 , "July", "August", "September", "October", "November", "December"},
    DayList = {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"},
    //Find the number of days between the end date and the start date
    NumberOfDates = Duration.Days(EndDate-StartDate),
    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
    //Turn this list into a table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}
                     , null, ExtraValues.Error),
    //Cast the single column in the table to type date
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
    
//Add custom columns for day of month, month number, year
    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),
    
//Since Power Query doesn't have functions to return day or month names,
    //use the lists created earlier for this
    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),
   
 DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),

    WeekEnding = Table.AddColumn(DayName, "Week Ending", each Date.EndOfWeek([Date])),
    #"Changed Type" = Table.TransformColumnTypes(WeekEnding ,{{"DayOfMonth", Int64.Type}, {"MonthNumberOfYear", Int64.Type}, {"Year", Int64.Type}, {"DayOfWeekNumber", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthYear", each Text.Range([MonthName], 0, 3) & "-" & Number.ToText([Year])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MonthYearNumber", each [Year] * 1000 + [MonthNumberOfYear]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"MonthYearNumber", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Week Ending", "Copy of Week Ending"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Week Ending", "WeekEndingDate"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"WeekEndingDate", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"Week Ending", "WeekEnding"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Date", Order.Descending}}),

IsInPreviousMonth = Table.AddColumn(IsInPreviousMonth, "IsInPreviousMonth", each Date.IsInPreviousMonth([Date]))



in
    #"Sorted Rows"
//in
//    CreateDateTable

 

So I just took another look and worked out I could change the code

 

  //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 2, 0)),

previously this was

 //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

Gather that 2 moves it one day forward.
Not sure how that works but it seems to do the job. Now back to the formula.

 

 

The #duration() constructor creates a Duration type, where the four numeric values correspond to days, hours, minutes, second. That List.Dates() that you're using increments by one day and two minutes at each step. List.Dates() can only return a date data type, though, so those minutes end up truncated from the output data values.

 

Here's what I use as my baseline date dimension:

 

let
    Source = List.Dates(
  #date(2010,1,1)
  ,Duration.Days( #date(2025,12,31) - #date(2010,1,1) ) + 1
  ,#duration(1,0,0,0) ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    Year = Table.AddColumn(#"Changed Type", "Year", each Date.Year( [Date] )),
    QuarterNumber = Table.AddColumn(Year, "QuarterNumber", each Date.QuarterOfYear( [Date] )),
    FirstYear = List.First( QuarterNumber[Year] ),
    QuarterIndex = 
            Table.AddColumn(QuarterNumber, "QuarterIndex", each let
          Mult = [Year] - FirstYear
          ,Index = 4 * Mult + [QuarterNumber]
        in
          Index),
    Quarter = Table.AddColumn(QuarterIndex, "Quarter", each Number.ToText( [Year] ) & " Q" & Number.ToText( [QuarterNumber] )),
    MonthNumber = Table.AddColumn(Quarter, "MonthNumber", each Date.Month( [Date] )),
    MonthIndex = Table.AddColumn(MonthNumber, "MonthIndex", each let
  Mult = [Year] - FirstYear
  ,Index = 12 * Mult + [MonthNumber]
in
  Index),
    Month = Table.AddColumn(MonthIndex, "Month", each Date.ToText( [Date], "yyyy MMM" )),
    MonthName = Table.AddColumn(Month, "MonthName", each Date.ToText( [Date], "MMMM" )),
    MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText( [Date], "MMM" )),
    WeekNumber = Table.AddColumn(MonthNameShort, "WeekNumber", each Date.WeekOfYear( [Date] )),
    WeekIndex = Table.AddColumn(WeekNumber, "WeekIndex", each let
  Mult = [Year] - FirstYear
  ,Index = 53 * Mult + [WeekNumber]
in
  Index),
    Week = Table.AddColumn(WeekIndex, "Week", each Number.ToText( [Year] ) & " W" & Number.ToText( [WeekNumber], "00" )),
    DayOfYear = Table.AddColumn(Week, "DayOfYear", each let
  Leap = Date.IsLeapYear( [Date] )
  ,DOY = Date.DayOfYear( [Date] )
in
  if not Leap 
    and [Date] >= #date([Year],3,1)
  then DOY + 1
  else DOY),
    DayOfQuarter = Table.AddColumn(DayOfYear, "DayOfQuarter", each let
  Leap = Date.IsLeapYear( [Date] )
  ,DOQ =
    Duration.Days(
      [Date] - Date.StartOfQuarter( [Date] ) )
    + 1
in
  if not Leap 
    and [QuarterNumber] = 1 
    and [Date] >= #date([Year],3,1)
  then DOQ + 1
  else DOQ),
    DayOfMonth = Table.AddColumn(DayOfQuarter, "DayOfMonth", each Date.Day( [Date] )),
    DayOfWeek = Table.AddColumn(DayOfMonth, "DayOfWeek", each Date.DayOfWeek( [Date] )),
    DayName = Table.AddColumn(DayOfWeek, "DayName", each Date.ToText( [Date], "dddd" )),
    DayNameShort = Table.AddColumn(DayName, "DayNameShort", each Date.ToText( [Date], "ddd" )),
    Weekday = Table.AddColumn(DayNameShort, "Weekday", each if [DayName] = "Saturday" or [DayName] = "Sunday"
then "Weekend"
else "Weekday"),
    WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday"),
    MergeHolidays = Table.NestedJoin(WeekdayFlag,{"Date"},Holidays,{"Date"},"NewColumn",JoinKind.LeftOuter),
    ExpandHolidays = Table.ExpandTableColumn(MergeHolidays, "NewColumn", {"HolidayName"}, {"HolidayName"}),
    HolidayFlag = Table.AddColumn(ExpandHolidays, "HolidayFlag", each [HolidayName] <> null),
    WorkdayFlag = Table.AddColumn(HolidayFlag, "WorkdayFlag", each [WeekdayFlag] and not [HolidayFlag]),
    CurrentYear = Table.AddColumn(WorkdayFlag, "CurrentYear", each Date.IsInCurrentYear( [Date] )),
    CurrentQuarter = Table.AddColumn(CurrentYear, "CurrentQuarter", each Date.IsInCurrentQuarter( [Date] )),
    CurrentMonth = Table.AddColumn(CurrentQuarter, "CurrentMonth", each Date.IsInCurrentMonth( [Date] )),
    CurrentWeek = Table.AddColumn(CurrentMonth, "CurrentWeek", each Date.IsInCurrentWeek( [Date] )),
    Today = Table.AddColumn(CurrentWeek, "Today", each [Date] = DateTime.Date( DateTime.LocalNow() )),
    CurrentYTD = Table.AddColumn(Today, "CurrentYTD", each Date.IsInYearToDate( [Date] )),
    CurrentQTD = Table.AddColumn(CurrentYTD, "CurrentQTD", each [CurrentQuarter]
and [Date] <= DateTime.Date( DateTime.LocalNow() )),
    CurrentMTD = Table.AddColumn(CurrentQTD, "CurrentMTD", each [CurrentMonth]
and [Date] <= DateTime.Date( DateTime.LocalNow() )),
    CurrentWTD = Table.AddColumn(CurrentMTD, "CurrentWTD", each [CurrentWeek]
and [Date] <= DateTime.Date( DateTime.LocalNow() )),
    #"Changed Type1" = Table.TransformColumnTypes(CurrentWTD,{{"Year", Int64.Type}, {"QuarterNumber", Int64.Type}, {"DayOfYear", Int64.Type}, {"DayOfQuarter", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DayOfWeek", Int64.Type}, {"WeekNumber", Int64.Type}, {"MonthNumber", Int64.Type}, {"QuarterIndex", Int64.Type}, {"MonthIndex", Int64.Type}, {"CurrentYear", type logical}, {"CurrentYTD", type logical}, {"WeekIndex", Int64.Type}, {"Quarter", type text}, {"Month", type text}, {"Week", type text}, {"DayName", type text}, {"Weekday", type text}, {"WeekdayFlag", type logical}, {"CurrentQuarter", type logical}, {"CurrentMonth", type logical}, {"CurrentWeek", type logical}, {"Today", type logical}, {"CurrentQTD", type logical}, {"CurrentMTD", type logical}, {"CurrentWTD", type logical}, {"DayNameShort", type text}, {"MonthName", type text}, {"MonthNameShort", type text}, {"HolidayFlag", type logical}, {"WorkdayFlag", type logical}})
in
    #"Changed Type1"

 

 

Edit: forgot to address the problem in your IsInPreviousMonth code:

 

IsInPreviousMonth = Table.AddColumn(IsInPreviousMonth, "IsInPreviousMonth", each Date.IsInPreviousMonth([Date]))

The table which you are adding this column to must be a prior defined binding in the let block. You are referencing the table defined by the current binding. It should beL

IsInPreviousMonth = Table.AddColumn(#"The previous step, not IsInPreviousMonth", "IsInPreviousMonth", each Date.IsInPreviousMonth([Date]))

 

Hi @greggyb, I tried loading your date dimension in but get an error around holidays.

2016-02-23 09_01_42-Query1 - Query Editor.png2016-02-23 09_01_57-Query1 - Query Editor.png

Any ideas?

 

Would be good to try and load up yours and give it a shot. I never thought of having leap year workarounds included. Always just accepted a little gap in my graphs. Smiley Very Happy

 

 

 

@elliotdixon, apologies. I have a merge in there to a holiday table that would be expected to be maintained by hand by client resources - that could live in a flat file or be hand-entered in Power BI, or live in a relational source.

 

The following snippet should be altered:

// Power Query
// Snippet from date dimension above
....
WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday"),
    MergeHolidays = Table.NestedJoin(WeekdayFlag,{"Date"},Holidays,{"Date"},"NewColumn",JoinKind.LeftOuter),
    ExpandHolidays = Table.ExpandTableColumn(MergeHolidays, "NewColumn", {"HolidayName"}, {"HolidayName"}),
    HolidayFlag = Table.AddColumn(ExpandHolidays, "HolidayFlag", each [HolidayName] <> null),
    WorkdayFlag = Table.AddColumn(HolidayFlag, "WorkdayFlag", each [WeekdayFlag] and not [HolidayFlag]),
....

// Comment and alter as follows:
...
WeekdayFlag = Table.AddColumn(Weekday, "WeekdayFlag", each [Weekday] = "Weekday"),
    // MergeHolidays = Table.NestedJoin(WeekdayFlag,{"Date"},Holidays,{"Date"},"NewColumn",JoinKind.LeftOuter),
    // ExpandHolidays = Table.ExpandTableColumn(MergeHolidays, "NewColumn", {"HolidayName"}, {"HolidayName"}),
    // HolidayFlag = Table.AddColumn(ExpandHolidays, "HolidayFlag", each [HolidayName] <> null),
    WorkdayFlag = Table.AddColumn(HolidayFlag, "WorkdayFlag", each [WeekdayFlag] ), // and not [HolidayFlag]),
....

Cheers @greggyb - its still trying to add a HolidayFlag column.

 

holdiay flag.png

 

 

You'll have to change the table reference in the WorkdayFlag step to a table other than that defined in the HolidayFlag step.

Djorran
Regular Visitor

Hi,

When you use dateadd() like that you get one whole month. So if the date is the 10th of october you will see the value from the 10th of september to the 10th of october.

 

If you use dateadd() you first need to calculate how many days there is to the first date in that month.

 

Kind regards,

Djorran

konstantinos
Memorable Member
Memorable Member

There is no filter context ( unless you use a slicer ) in a card that has a month. You need to pass a filter in the formula

 

maybe this works or something similar 

 

current MTD =
CALCULATE (
    [MTD WO Amount];
    FILTER (
        ALL ( DimDate );
        DimDate[Year] = YEAR ( MAX ( DimDate[DateKey] ) )
            && DimDate[DateKey] = MONTH ( DimDate[DateKey] )
    )
)

 

 

Konstantinos Ioannou
cwayne758
Helper IV
Helper IV

*UPDATE* Month Date Write Off Amount does not give me the current MTD value. 

 

Could someone possibly explain why? Or when is an appropriate time to use DATEADD

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.