Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
marypal
Frequent Visitor

Clustered column chart, compare two periods

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:target clients.png

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
target clients comp.png

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

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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 ) )
    )

 

ppm1_1-1682857156388.png

Pat

Microsoft Employee

View solution in original post

2 REPLIES 2
ppm1
Solution Sage
Solution Sage

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 ) )
    )

 

ppm1_1-1682857156388.png

Pat

Microsoft Employee
marypal
Frequent Visitor

Hello @ppm1 ,

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.