Two measures in a matrix - Previous year total sales and current year monthly sales

[Sydsid]

Hi

i am new to power bi. i have requirement to build dashboard for income statement in matrix layout. Need to show for every Line code, previous year total actuals and current year's monthly actual and forecast depending upon the selected year and month from the slicer.

the first column has to show header name "Previous Year" and followed by jan to dec with either sum of actuals or sum of forecast depending on the current month.

I am unable to show three metrics without being mutually exclusive.. i.e. i cant use IF or switch logic.

Any suggestion is much appreciated.

Thank you

Sid

[Sydsid]

In addition to above, the additional request is to show 0 for Previous Year if there is no data available.

Thank you

Sid

[Sydsid]

Thanks Rita.

The link to the pbix file is https://drive.google.com/file/d/1_j994pm-9CuLoNwnkflRGtKDd83xPHfR/view?usp=sharing

Please do let me know if you face an issue downloading it.

Below is the screenshort of the layout requested for the Income statement. I m struggling to put YTD total for previous year along with two other measures (Actuals and Forecast) measured for the months of reporting year. In the shared pbix file, I managed to report Actual / Forecast in brackets with dynamic column header names. 

Below are the key measures / columns definitions that i m struggling with to present "Previous Year" along with months of the selected year in slicer. Most important measure to look at is 'SwitchedMeasure'. I feel it need an alternate function or logic in the place Switch function, so that all three measures (Previou Year, Actual Months and Forecast Months) can be shown instead of mutually excluding.

DynamicColumnHeader =

SWITCH(

    TRUE(),

    'Dim Date'[Month Number] = 0, "PREVIOUS YEAR",

    'Dim Date'[Month Number] < MONTH(TODAY()), "ACTUAL",

    "FORECAST"

 )

MonthFactType = FORMAT('Dim Date'[Date].[Date],"mmm") &" - " & 'Dim Date'[Year] & UNICHAR(10) & "(" &'Dim Date'[DynamicColumnHeader] &")"

SwitchedMeasure =

VAR CurrentMonth = MONTH(TODAY())

VAR SelectedMonth = SELECTEDVALUE('Dim Date'[Month Number])

VAR SelectedYear = SELECTEDVALUE('Dim Date'[Year])

VAR PreviousYr = SelectedYear - 1

// Calculate Actuals and Forecasts based on the selected month and year

VAR ActualsValue =

    CALCULATE(

        SUM('Fact Actuals'[Actual])

    )

VAR ForecastsValue =

    CALCULATE(

        SUM('Fact Forecast'[Actual])

    )

// Check if the context is for the Previous Year Total

VAR IsPreviousYearTotal =

    NOT(ISFILTERED('Dim Date'[Month Number])) || ISBLANK(SelectedMonth)

RETURN

SWITCH(

    TRUE(),

    IsPreviousYearTotal,

    // Show Previous Year Total for the entire previous year

    CALCULATE(

        SUM('Fact Actuals'[Actual]),

        'Dim Date'[Year] = PreviousYr

    ),

    // For current year calculations

    IF(

        SelectedYear = YEAR(TODAY()) && CurrentMonth < MONTH(TODAY()),

        ActualsValue,

        IF(

            SelectedYear = YEAR(TODAY()) && CurrentMonth >= MONTH(TODAY()),

            ForecastsValue,

            IF(

                SelectedYear <> YEAR(TODAY()),

                ActualsValue,

                BLANK()

            )

        )

    )

)

[Ritaf1983]

Hi @Sydsid 

Please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly