Forum Discussion

bmk's avatar
bmk
Helper II
2 years ago
Solved

Previous Month DAX Calculation

I seem to be running into a road block and I think this may be straightforward. If someone can please help. 

 

I am using a table visual on PBI, with a bunch of measures. I have a date table. I wish to calculate a DAX filter formula on the date table to give me dates of the previous month relative to today. Assuming today's date is January 2024, I want all calculations and measures only from Dec 2023. The date table i'm using is : 

Date Table =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( today() -9999), MONTH ( today() - 9999), DAY ( today()- 9999) ),
        DATE ( YEAR ( today() ), MONTH ( today() ), DAY ( today() ) )
    ),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
        & FORMAT ( [Date], "Q" ),
    "Start of Week",[Date]  - WEEKDAY([Date],2) +1,
    "Week Ending",[Date] + 7 - WEEKDAY([DATE],2),
    "Week Range",[Date]  - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2),
    "MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ),
    "WeekNum",weeknum([Date],2)-1
)
 
and the DAX I tried is :
IsPreviousMonth =
    IF(
        'Date Table'[Date] >= STARTOFMONTH('Date Table'[TodayDate])
        && 'Date Table'[Date] < STARTOFMONTH('Date Table'[TodayDate]) - 1,
        1,
        0
    )
Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    bmk 

    Add calculated column as follows:

    Previous Month = INT('Date Table'[Date] IN CALENDAR( EOMONTH( TODAY() , -2)+1 , EOMONTH( TODAY() , -1) ))


    I suggest you modify your calender table date range as follows, not to have too many dates/rows. I entere 10 years back so you can chagne it in terms of years:

    ADDCOLUMNS (
    CALENDAR (
    DATE ( YEAR ( today() ) - 10 ) ,1 ,1 ),
    DATE ( YEAR ( today() ),12 ,31 ),




7 Replies

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago
    Hello,
     
    This returns a table with all dates(datetime) from previous month. Please replace first parameter with your calendar table.
    DATESBETWEEN( 'Calendar'[MasterDate], EOMONTH(TODAY(), -2) + 1 , EOMONTH(TODAY(), -1))
  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    bmk 

    Add calculated column as follows:

    Previous Month = INT('Date Table'[Date] IN CALENDAR( EOMONTH( TODAY() , -2)+1 , EOMONTH( TODAY() , -1) ))


    I suggest you modify your calender table date range as follows, not to have too many dates/rows. I entere 10 years back so you can chagne it in terms of years:

    ADDCOLUMNS (
    CALENDAR (
    DATE ( YEAR ( today() ) - 10 ) ,1 ,1 ),
    DATE ( YEAR ( today() ),12 ,31 ),




    • bmk's avatar
      bmk
      Helper II
      2 years ago

      Fowmy, the calculated column worked great, thank you. I did try to modify the Date DAX overall as per your suggestion, but I am getting a DAX syntax error. Can you please check what I am missing.


      Date Table =
      ADDCOLUMNS (
      CALENDAR (
      DATE ( YEAR ( today() ) - 10 ) ,1 ,1 ),
      DATE ( YEAR ( today() ),12 ,31 ),
      ),
      "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
      "Year", YEAR ( [Date] ),
      "Monthnumber", FORMAT ( [Date], "MM" ),
      "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
      "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
      "MonthNameShort", FORMAT ( [Date], "mmm" ),
      "MonthNameLong", FORMAT ( [Date], "mmmm" ),
      "DayOfWeekNumber", WEEKDAY ( [Date] ),
      "DayOfWeek", FORMAT ( [Date], "dddd" ),
      "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
      "Quarter", "Q" & FORMAT ( [Date], "Q" ),
      "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
      & FORMAT ( [Date], "Q" ),
      "Start of Week",[Date] - WEEKDAY([Date],2) +1,
      "Week Ending",[Date] + 7 - WEEKDAY([DATE],2),
      "Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2),
      "MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ),
      "WeekNum",weeknum([Date],2)-1
      )

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        bmk 

        I fixed it, please try now.

        Date Table = 
ADDCOLUMNS (
CALENDAR (
    DATE ( YEAR ( today() ) - 10  ,1 ,1 ),
    DATE ( YEAR ( today() ), 12 ,31 )
),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
& FORMAT ( [Date], "Q" ),
"Start of Week",[Date] - WEEKDAY([Date],2) +1,
"Week Ending",[Date] + 7 - WEEKDAY([DATE],2),
"Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2),
"MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ),
"WeekNum",weeknum([Date],2)-1
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi bmk ,

     

    I've changed your code., you can try below dax:

    Date Table = 
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( today() ) - 10 ,1 ,1 ),
DATE ( YEAR ( today() ),12 ,31 )),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
& FORMAT ( [Date], "Q" ),
"Start of Week",[Date] - WEEKDAY([Date],2) +1,
"Week Ending",[Date] + 7 - WEEKDAY([DATE],2),
"Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2),
"MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ),
"WeekNum",weeknum([Date],2)-1
)

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly