Date Grouping

[DanielaAttard]

Hi,

I have a line chart showing data during a period compared to the same period of previous year. Example below.

When using the bins to group the data monthly instead of daily it doesn't work and shows just the current year data.

Can someone please advise how to group the data into monthly instead of daily that would show both the line for current year and the line for previous year on same visual ?

[danextian · Accepted solution]

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.

[Anonymous · Accepted solution]

Hi @DanielaAttard ,

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.

[dharmendars007]

Hello @DanielaAttard , 

Instead of adding Dates you can choose month & Year Column if it is avaible or you can create one.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[DanielaAttard]

I tried that as per below. What am I missing please?

[danextian · Accepted solution]

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.

[danextian]

Hi @DanielaAttard 

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.

[DanielaAttard]

I already tried that as per below. What am I missing please?