Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Combo chart with relative axis

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!!:robotvery-happy:

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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 :robotlol:

     

    Thanks again,

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

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

    Combo_chart_with_relative_axis

     

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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 :robotlol:

       

      Thanks again,