Forum Discussion

Rich_Wyeth's avatar
Rich_Wyeth
Helper I
1 year ago
Solved

Looking for Previous Period Comparision

Hi,   I am creating a report that needs to look at a date range selected and to look back a year at the same period.   I created some measures, example:  M_TPRICE = SUM('Margin Reports'[T PRICE...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Rich_Wyeth , Go to modelling create a new table using

     

    DAX
    Calendar =
    ADDCOLUMNS (
    CALENDAR (DATE(2020, 1, 1), DATE(2023, 12, 31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "Quarter", QUARTER([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "YearMonth", FORMAT([Date], "YYYY-MM")
    )

     

    Change date as per your dates and create a relationship of this with original date column

Rich_Wyeth's avatar
Rich_Wyeth
Helper I
1 year ago

Hi,

 

One thing I do notice is that my date column, is just that, a date column. It is not a Calendar column, so doesn't split out into Day, Month, Year, Quarter, when I use it.

 

Could this be my problem, as dateadd doesn't work either.

 

How can I make the date column become a calendar, rather than just a date "Long Date".?

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Rich_Wyeth , Go to modelling create a new table using

 

DAX
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2023, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Quarter", QUARTER([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)

 

Change date as per your dates and create a relationship of this with original date column