Clustered column chart, compare two periods

[marypal]

Hello,

I need to create the following report.

I choose a report month on a slicer on the first page (for example March 2023).

Then I need to display a clustered column chart for target clients for the previous 12 months (Apr 2022-Mar 2023). And also compare this data with the previous year. The visualisation should look like this:

What I did. I have 2 dim_dates tables: slicer is linked to report_dates table, fact table is linked with dim_dates table.

Created measures:

Gives me the end of the report period: EndDate = MAX('report_dates'[short_date])
Gives me the start of 1-year period: 1YearStartDate = EOMONTH([EndDate],-12)+1

Measure that counts target clients for the previous 12 month: Count Target1Y = CALCULATE(SUM('fact_table' [target_client]),KEEPFILTERS(DATESBETWEEN('dim_dates'[short_date],[1YearStartDate],[EndDate])))

'fact_table'[target_client]) is either 1 or 0.

Measure that counts target clients for the same period last year: Count Target1YLastYear =

VAR curPeriod = DATESBETWEEN('report_dates'[short_date],[1YearStartDate],[EndDate])

VAR prevPeriod = DATEADD(DATESBETWEEN('dim_dates'[short_date],[1YearStartDate],[EndDate]),-1,YEAR)

RETURN

CALCULATE(CALCULATE(SUM('fact_table'[target_client]),KEEPFILTERS(curPeriod)),KEEPFILTERS(prevPeriod))

But I have visualisation like this

I also tried the same table dim_dates in curPeriod and prevPeriod, but this does not work.

Could you please tell me how to resolve this task?

Many thanks

[ppm1 · Accepted solution]

Here's one way to do it.

1. Make sure your 2nd date table is disconnected (no relationship)

2. Use the month column from your 1st date table in the visual and your 2nd date table in the slider.

3. Create two measures like this (the Date2 table is the one in the slicer).

Last 12 Mos CY =
VAR maxdate2 =
    MAX ( 'Date2'[Date] )
VAR maxdate1 =
    MAX ( 'Date'[Date] )
VAR mindate =
    EOMONTH ( maxdate2, -12 ) + 1
RETURN
    IF ( maxdate1 <= maxdate2 && maxdate1 >= mindate, [Total Qty] )

Last 12 Mos PY =
VAR maxdate2 =
    MAX ( 'Date2'[Date] )
VAR maxdate1 =
    MAX ( 'Date'[Date] )
VAR mindate =
    EOMONTH ( maxdate2, -12 ) + 1
RETURN
    IF (
        maxdate1 <= maxdate2
            && maxdate1 >= mindate,
        CALCULATE ( [Total Qty], DATEADD ( 'Date'[Date], -12, MONTH ) )
    )

Pat

[marypal]

Hello @ppm1 ,

Thank you so much!!! It's working!