Forum Discussion
Date Grouping
Use the calculated table in my reply. Mark the calendar as a dates table. Sort Month name by month number. Disable time auto date/time. Add both year and month columns from the calendar table (not from the auto-generated hierarchy) to the viz. Expand the hierarchy in the viz. Apply your PY calculation to the DateTable columns and not to the one from your fact table.
Hi Anonymous ,
Please refers to the following steps.
Create a calendar table.Calendar = ADDCOLUMNS( CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])), "Year",YEAR([Date]), "Month",MONTH([Date]), "Year-Month",FORMAT([Date],"yyyy-mm") )
The model is as follows.The Total PY measure is shown below.
TotalPY = CALCULATE(SUM('Table'[Amount]), DATEADD('Calendar'[Date],-1,YEAR) )
Use the Year-Month column of the calendar table as the X-axis, Total CY as the Y-axis, and Total PY as the secondary Y-axis to build the line chart.
The final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Use a separate dates table that contains columns for several date elements - year, quarter, month number, month name, month and year etc. Mark that as a dates table and create a a one-to-many single direction relationshiop from the date column from the dates table and the one from the fact table. Below is a basic DAX calculated table formula
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" )
)
Attached is a sample pbix with date tables created in either M or DAX.
I already tried that as per below. What am I missing please?