Combo chart with relative axis

[Anonymous]

Hi there, 

I'm looking for a solution to a problem that might be much simpler than what i'm imagining.

The thing is, i want to create a really simple combo chart where sales amount is described by bars and sales units by lines.

Something like this:

Now, the tricky part (for me) is making a relative axis depending on today's date. I tried creating a calculated column in my dates table, but seems to be is not that straight forward. For example, if today's date is March the 7th, 'Last month' should get the sales from february, and 'Last 3 months' the sales from november, december and january.

Creating calculated measures for 'last month' and 'last 3 months' is not an alternative, because there will be no conection between the bars or lines to show increase or decrease among those periods.

So... any ideas on how to create this chart?

Thanks!!

[v-jiascu-msft]

Hi @Anonymous,

Seems easy, but very tricky. Please try the formula below. I changed the date of my computer to test. It works fine.

x-axis =
VAR yyyymm =
    FORMAT ( [Date], "yyyymm" )
VAR lastmonth =
    CALCULATE (
        FORMAT ( MIN ( [Date] ), "yyyymm" ),
        DATESINPERIOD ( 'Calendar'[Date], TODAY (), -1, MONTH ),
        ALL ( 'Calendar' )
    )
VAR last3months =
    CALCULATE (
        FORMAT ( MIN ( [Date] ), "yyyymm" ),
        DATESINPERIOD ( 'Calendar'[Date], TODAY () - DAY ( TODAY () ), -4, MONTH )
    )
RETURN
    IF (
        yyyymm = lastmonth,
        "lastmonth",
        IF ( yyyymm = last3months, "last3month", "Other" )
    )

Best Regards,

Dale

[Anonymous · Accepted solution]

Dale, thanks a lot! 

I did a little change to it and it worked perfectly.

I just replicated two more variables, because 'last3months' wouldn't get month-3 and month-2. Then i used a nested if and voilá!

This are the changes:

x-axis = 
VAR yyyymm =
    FORMAT ( [FECHA], "yyyymm" )
VAR lastmonth =
    CALCULATE (
        FORMAT ( MIN ( [FECHA] ), "yyyymm" ),
        DATESINPERIOD ( FECHAS[FECHA], TODAY (), -1 , MONTH ),
        ALL ( FECHAS )
    )
VAR month4 =
    CALCULATE (
        FORMAT ( MIN ( [FECHA] ), "yyyymm" ),
        DATESINPERIOD ( FECHAS[FECHA], TODAY () - DAY ( TODAY () ), -4 , MONTH )
    )
VAR month3 =
    CALCULATE (
        FORMAT ( MIN ( [FECHA] ), "yyyymm" ),
        DATESINPERIOD ( FECHAS[FECHA], TODAY () - DAY ( TODAY () ), -3 , MONTH )
    )    
VAR month2 =
    CALCULATE (
        FORMAT ( MIN ( [FECHA] ), "yyyymm" ),
        DATESINPERIOD ( FECHAS[FECHA], TODAY () - DAY ( TODAY () ), -2 , MONTH )
    )       
  
RETURN
    IF (
        yyyymm = lastmonth,
        "lastmonth",
        IF ( yyyymm = month4, "last3month", IF(yyyymm = month3, "last3month", IF(yyyymm = month2, "last3month", "other" )))
    )

You truly are a Super Contributor 

Thanks again,