2

I tried to make two area charts using the Deneb visual of power bi and vega lite language. The two area charts present the progress of a measure through the months of 2022 and 2023.

What I want to do :

1) Display a forecast line (with dashed line) of the 2023 area chart starting from the month of "June" based on the numbers in of 2022 and the previous months of 2023 to make the forecast become more accurate.

==> For example a peak was reached in December 2022 , meaning that a peak in December 2023 would be mostly be reached too with a bigger value

2) I don't know why the months are not placed for me in odrer

enter image description here

Here's the PBIX file: https://drive.google.com/file/d/1P16eirLEWCdGsPq8ZS582Wi4MRaP8ppS/view?usp=sharing

Here's the code:

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "format(datum['Leaves Count 2022']/1000,'0.1f')+'k'",
      "as": "l1"
    },
    {
      "calculate": "format(datum['Leaves Count 2023']/1000,'0.1f')+'k'",
      "as": "l2"
    },
    {
      "joinaggregate": [
        {
          "op": "max",
          "field": "Leaves Count 2022",
          "as": "l1max"
        },
        {
          "op": "max",
          "field": "Leaves Count 2023",
          "as": "l2max"
        },
        {
          "op": "min",
          "field": "Leaves Count 2022",
          "as": "l1min"
        },
        {
          "op": "min",
          "field": "Leaves Count 2023",
          "as": "l2min"
        }
      ]
    }
  ],
  "layer": [
    {
      "mark": {
        "type": "area",
        "line": {"color": "#063970"},
        "color": {
          "x1": 1,
          "y1": 1,
          "gradient": "linear",
          "stops": [
            {
              "offset": 0,
              "color": "white"
            },
            {
              "offset": 1,
              "color": "#063970"
            }
          ]
        }
      }
    },
    {
      "mark": {
        "type": "area",
        "line": {"color": "#2596be"},
        "color": {
          "x1": 1,
          "y1": 1,
          "gradient": "linear",
          "stops": [
            {
              "offset": 0,
              "color": "white"
            },
            {
              "offset": 1,
              "color": "#2596be"
            }
          ]
        }
      },
      "encoding": {
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "circle",
        "size": 50,
        "fill": {
          "expr": "datum['Leaves Count 2023'] == datum.l2max ? 'red' : datum['Leaves Count 2023'] == datum.l2min ? 'green' : '#063970'"
        },
        "stroke": "white",
        "strokeWidth": 1
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2023",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "circle",
        "size": 50,
        "fill": {
          "expr": "datum['Leaves Count 2022'] == datum.l1max ? 'red' : datum['Leaves Count 2022'] == datum.l1min ? 'green' : '#2596be'"
        },
        "stroke": "white",
        "strokeWidth": 1
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "yOffset": -10,
        "size": 10,
        "color": {
          "expr": "datum['Leaves Count 2023'] == datum.l2max ? 'red' : datum['Leaves Count 2023'] == datum.l2min ? 'green' : '#000000'"
        },
        "fontWeight": {
          "expr": "datum['Leaves Count 2023'] == datum.l2max || datum['Leaves Count 2023'] == datum.l2min ? 'bold' : 'normal'"
        }
      },
      "encoding": {
        "text": {"field": "l2"},
        "opacity": {
          "condition": {
            "test": {
              "field": "MONTH",
              "equal": "off"
            },
            "value": 0.1
          },
          "value": 1
        },
        "y": {
          "field": "Leaves Count 2023",
          "type": "quantitative",
          "axis": null
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "yOffset": -10,
        "size": 10,
        "color": {
          "expr": "datum['Leaves Count 2022'] == datum.l1max ? 'red' : datum['Leaves Count 2022'] == datum.l1min ? 'green' : '#000000'"
        },
        "fontWeight": {
          "expr": "datum['Leaves Count 2022'] == datum.l1max || datum['Leaves Count 2022'] == datum.l1min ? 'bold' : 'normal'"
        }
      },
      "encoding": {
        "text": {"field": "l1"},
        "opacity": {
          "condition": {
            "test": {
              "field": "MONTH",
              "equal": "off"
            },
            "value": 0.1
          },
          "value": 1
        },
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative",
          "axis": null
        }
      }
    },
    {
      "mark": {
        "type": "rule",
        "stroke": "red",
        "strokeDash": [3, 3]
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2023",
          "type": "quantitative"
        }
      },
      "transform": [
        {"filter": "datum['Leaves Count 2023'] == datum.l2max"}
      ]
    },
    {
      "mark": {
        "type": "rule",
        "stroke": "red",
        "strokeDash": [3, 3]
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative"
        }
      },
      "transform": [
        {"filter": "datum['Leaves Count 2022'] == datum.l1max"}
      ]
    }
  ],
  "encoding": {
    "x": {
      "field": "MONTH",
      "type": "ordinal",
      "axis": {"labelPadding": 0},
      "title": null
    },
    "y": {
      "field": "Leaves Count 2023",
      "type": "quantitative",
      "axis": null
    }
  }
}
userrr
  • 197
  • 7
  • 1
    Hi Mariam - I suggest splitting your question into two (keep questions to a single problem). I can certainly solve the second question but I think you'll struggle with seasonal forecasting. PBI does support this on native line charts but it is very limited. There is no DAX equivalent of Excel's FORECAST.ETS which is what you would require. https://www.youtube.com/watch?v=j22tLUQQDh4 – Davide Bacci Jul 02 '23 at 11:53
  • @DavideBacci Yes sadly. The native line chart forecast functionnality is very limited but yeah :( I don't know what would be the solution to be honest, I sorta of wanted to do everything with the Deneb visual – userrr Jul 02 '23 at 19:24
  • 1
    Deneb with Vega is very powerful (see here for some examples of it being pushed: https://github.com/PBI-David/Deneb-Showcase) but stuff like seasonal forecasting and clustering is not really possible at the moment. Hopefully, they'll implement options for us in DAX or as a transform but it won't be any time soon. – Davide Bacci Jul 02 '23 at 19:57

0 Answers0