Search code examples
powerbidaxpowerbi-desktop

Power BI order linechart by MonthName on X-Axis and RunningMonth in Tooltip

In my datamodel I have sales over years in different SalesEvents. I need to compare the sales Quantity (Y-Axis) for multipe EventNames (Legend) over Monthnames (X-Axis).
If I use the RunningMonth that would be months from first order on each EventName the visula works.
enter image description here

But I need to build the visual to show the MonthName ordered by RunningMonth since a SalesEvent can go longer then one year. This does however not work, since the order by Tooltip failes due to the automatic Sum:
enter image description here

I have tried a measure for sorting in the Tooltip like I have found on other poasts but due to my multiple EventNames it seams not to be working:

Sorter = 
SWITCH( VALUES(Orders[SortABC]),
"1",1,
"2",2,
"3",3,
"4",4,
"5",5,
"6",6,
"7",7,
"8",8,
"9",9,
"10",10,
"11",11,
"12",12,
"13",13,
"14",14,
"15",15,
"16",16,
17
)

Here is the example data if anyone can help me.

with upa as
(
select 'Sales19' as EventName, 1 as RunningMonth,  cast('28.11.2019' as Date) as CreatedOn, 100 as Quantity union all
select 'Sales19' as EventName, 2 as RunningMonth,  cast('31.12.2019' as Date) as CreatedOn, 200 as Quantity union all
select 'Sales19' as EventName, 3 as RunningMonth,  cast('02.02.2020' as Date) as CreatedOn, 150 as Quantity union all
select 'Sales19' as EventName, 4 as RunningMonth,  cast('06.03.2020' as Date) as CreatedOn, 70 as  Quantity union all
select 'Sales19' as EventName, 5 as RunningMonth,  cast('08.04.2020' as Date) as CreatedOn, 50 as  Quantity union all
select 'Sales19' as EventName, 6 as RunningMonth,  cast('11.05.2020' as Date) as CreatedOn, 40 as  Quantity union all
select 'Sales19' as EventName, 7 as RunningMonth,  cast('13.06.2020' as Date) as CreatedOn, 20 as  Quantity union all
select 'Sales19' as EventName, 8 as RunningMonth,  cast('16.07.2020' as Date) as CreatedOn, 30 as  Quantity union all
select 'Sales19' as EventName, 9 as RunningMonth,  cast('18.08.2020' as Date) as CreatedOn, 8 as   Quantity union all
select 'Sales19' as EventName, 10 as RunningMonth, cast('20.09.2020' as Date) as CreatedOn, -12 as Quantity union all
select 'Sales19' as EventName, 11 as RunningMonth, cast('23.10.2020' as Date) as CreatedOn, -20 as Quantity union all
select 'Sales19' as EventName, 12 as RunningMonth, cast('25.11.2020' as Date) as CreatedOn, 5 as   Quantity union all
select 'Sales19' as EventName, 13 as RunningMonth, cast('28.12.2020' as Date) as CreatedOn, 0 as   Quantity union all
select 'Sales19' as EventName, 14 as RunningMonth, cast('30.01.2021' as Date) as CreatedOn, 0 as   Quantity union all
select 'Sales19' as EventName, 16 as RunningMonth, cast('04.03.2021' as Date) as CreatedOn, -4 as  Quantity union all
select 'Sales23' as EventName, 1 as RunningMonth,  cast('02.12.2023' as Date) as CreatedOn, 125 as Quantity union all
select 'Sales23' as EventName, 2 as RunningMonth,  cast('07.01.2024' as Date) as CreatedOn, 250 as Quantity union all
select 'Sales23' as EventName, 3 as RunningMonth,  cast('12.02.2024' as Date) as CreatedOn, 187 as Quantity union all
select 'Sales23' as EventName, 4 as RunningMonth,  cast('19.03.2024' as Date) as CreatedOn, 87 as  Quantity union all
select 'Sales23' as EventName, 5 as RunningMonth,  cast('24.04.2024' as Date) as CreatedOn, 62 as  Quantity union all
select 'Sales23' as EventName, 6 as RunningMonth,  cast('30.05.2024' as Date) as CreatedOn, 50 as  Quantity union all
select 'Sales23' as EventName, 7 as RunningMonth,  cast('05.07.2024' as Date) as CreatedOn, 25 as  Quantity union all
select 'Sales23' as EventName, 8 as RunningMonth,  cast('10.08.2024' as Date) as CreatedOn, 37 as  Quantity union all
select 'Sales23' as EventName, 9 as RunningMonth,  cast('15.09.2024' as Date) as CreatedOn, 10 as  Quantity union all
select 'Sales23' as EventName, 10 as RunningMonth, cast('21.10.2024' as Date) as CreatedOn, -15 as Quantity union all
select 'Sales23' as EventName, 11 as RunningMonth, cast('26.11.2024' as Date) as CreatedOn, -25 as Quantity union all
select 'Sales23' as EventName, 13 as RunningMonth, cast('01.01.2025' as Date) as CreatedOn, 6 as   Quantity union all
select 'Sales23' as EventName, 14 as RunningMonth, cast('06.02.2025' as Date) as CreatedOn, 0 as   Quantity union all
select 'Sales23' as EventName, 15 as RunningMonth, cast('14.03.2025' as Date) as CreatedOn, 0 as   Quantity
)
select * from upa

Please help.

The result should look somthing like this. (The Sales23 line is drawn by me.) enter image description here

After putting in Sams ideas and filling the gabs in my DateTable it worked.
Then the last stepp occured, to put it in a RunningTotal. If I adjust my RunningTotal formula I get a flet line. How can I put all this into a running total?

Chart Value RT = IF(NOT ISBLANK([Chart Month]), 
    CALCULATE(
        SUM(Orders[Quantity]),
        FILTER(
            ALL(Orders),
            // Old part of function:
            // Orders[RunningMonth] <= MAX(Orders[RunningMonth])
            // New part of function:
            [Chart Value] <= [Chart Value]
            && Orders[EventName] = max(Orders[EventName]) 
        )
    )
)
Solution

  • Create a new Measure for the chart tooltip that will be used for sorting.

    Chart Month = 
  var chartFirstDate = CALCULATE(MIN('Orders'[CreatedOn]), ALLSELECTED('Orders'))
  var chartFirstMonth = MONTH(chartFirstDate)

  var thisFirstDate = CALCULATE(MIN('Orders'[CreatedOn]), ALLEXCEPT('Orders', 'Orders'[EventName]))
  var thisFirstMonth = MONTH(thisFirstDate)
  
  var monthStartDiff = IF(thisFirstMonth < chartFirstMonth, 12, 0) + thisFirstMonth - chartFirstMonth
  var thisMonthDuration = DATEDIFF(thisFirstDate, MIN('Orders'[CreatedOn]), MONTH)

  var result = 1 + thisMonthDuration + monthStartDiff

  return IF(result < 13, result)

    And create another new Measure for the chart value:

    Chart Value = IF(NOT ISBLANK([Chart Month]), SUM('Orders'[Quantity]))

    Use Chart Value for your Y-axis, and Chart Month for the Tooltips and sort as before.

    enter image description here

    For Running Total, you could use:

    Chart Value RT = 
  var maxD = EOMONTH(MIN('Orders'[CreatedOn]), 0)
  var result =
    CALCULATE(
      [Chart Value],
      ALLEXCEPT('Orders', 'Orders'[EventName]),
      'Orders'[CreatedOn] <= maxD
    )
  return IF(NOT ISBLANK([Chart Month]), result)