Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
Solved! Go to Solution.
@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
Proud to be a Super User! |
|
Hi @Rich_Wyeth
You can try with dateadd function .
or check this video for better understanding
https://www.youtube.com/watch?v=e_pB4MzGO2I
https://www.youtube.com/watch?v=Wwo-tY0B9pY&t=428s
https://www.youtube.com/watch?v=fP8oxU2DE9w
These videos would help you to write comparison for dynamic period either day/ week/month/ year is selected in date range.
I hope I answered your question!
@Rich_Wyeth , Use DATEADD function
DAX
M_TPRICE = SUM('Margin Reports'[T PRICE])
Previous Period Measure:
DAX
M_TPRICE_PreviousPeriod = CALCULATE(
[M_TPRICE],
DATEADD('Margin Reports'[DATE], -1, YEAR)
)
Difference Measure:
M_TPRICE_Difference = [M_TPRICE] - [M_TPRICE_PreviousPeriod]
Proud to be a Super User! |
|
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".?
@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
Proud to be a Super User! |
|
User | Count |
---|---|
120 | |
69 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |