Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
royalswe
Helper I
Helper I

Recreate excel table in power bi with dax

Hi! I'm new to Power BI and have struggled with a problem for three days now 😕

 

I'm trying to recreate my Excel table in Power BI looking like this

excellTable.png

The three first rows is the latest stock value in each month.

Forecasted: Is the latest forecasted value we think we need.

The last row is the three first row minus Forecasted row.

 

This is a sample of my two tables i'm using (The yellow marks is the latest value for each category)

excell help.png

In this case I want the yellow values to be in the table above.

Then I want all the last categories of each month summarized minus the latest Forecasted value.

I suppose this could be done with a Matrix table.

 

/ Cheers

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi @royalswe 

to solve this, I have created a sample report.

A short description of this file:
I have created two additional tables, vDate and MatrixRows. MatrixRows is necessary in order to get 'Forecasted' and 'Material' on the same axis as the materials.

 

Then there are 3 measures. The measure for the stock at the end of the month is a bit complex, because your requirement in the last row (Material - Forecasted), demands that the stock is calculated for each material at the last date for each material:

 

 

stock end of month =
SUMX (
    ADDCOLUMNS (
        VALUES ( Material[Material] );
        "m";
        VAR _mxDate =
            CALCULATE ( MAX ( Material[createdDate] ) )
        VAR _currentMaterial =
            CALCULATE ( VALUES ( Material[Material] ) )
        RETURN
            CALCULATE (
                SUM ( Material[quantity] );
                FILTER (
                    Material;
                    Material[createdDate] = _mxDate
                        && Material[Material] = _currentMaterial
                )
            )
    );
    [m]
)

 

 

 

The Forecast measure is pretty simple:

 

 

forecast =
VAR _maxDate =
    CALCULATE ( MAX ( Forecast[createdDate] ) )
RETURN
    CALCULATE (
        SUM ( Forecast[quantity] );
        FILTER ( vDate; vDate[Date] = _maxDate )
    )

 

 

 

And then there is the measure to apply these two measures according to the rows of the matrix:

 

 

matrixVariable = 
VAR sv =
    SELECTEDVALUE ( matrixRows[maRo] )
RETURN
    SWITCH (
        TRUE ();
        sv IN VALUES ( Material[Material] ); CALCULATE ( [stock end of month]; FILTER ( Material; [Material] = sv ) );
        sv = "Forecasted"; [forecast];
        sv = "Material - Forecasted"; [stock end of month] - [forecast];
        BLANK ()
    )

 

 

 

Next time you are in need of assistance from the forum, please show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

View solution in original post

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

Hi @royalswe 

to solve this, I have created a sample report.

A short description of this file:
I have created two additional tables, vDate and MatrixRows. MatrixRows is necessary in order to get 'Forecasted' and 'Material' on the same axis as the materials.

 

Then there are 3 measures. The measure for the stock at the end of the month is a bit complex, because your requirement in the last row (Material - Forecasted), demands that the stock is calculated for each material at the last date for each material:

 

 

stock end of month =
SUMX (
    ADDCOLUMNS (
        VALUES ( Material[Material] );
        "m";
        VAR _mxDate =
            CALCULATE ( MAX ( Material[createdDate] ) )
        VAR _currentMaterial =
            CALCULATE ( VALUES ( Material[Material] ) )
        RETURN
            CALCULATE (
                SUM ( Material[quantity] );
                FILTER (
                    Material;
                    Material[createdDate] = _mxDate
                        && Material[Material] = _currentMaterial
                )
            )
    );
    [m]
)

 

 

 

The Forecast measure is pretty simple:

 

 

forecast =
VAR _maxDate =
    CALCULATE ( MAX ( Forecast[createdDate] ) )
RETURN
    CALCULATE (
        SUM ( Forecast[quantity] );
        FILTER ( vDate; vDate[Date] = _maxDate )
    )

 

 

 

And then there is the measure to apply these two measures according to the rows of the matrix:

 

 

matrixVariable = 
VAR sv =
    SELECTEDVALUE ( matrixRows[maRo] )
RETURN
    SWITCH (
        TRUE ();
        sv IN VALUES ( Material[Material] ); CALCULATE ( [stock end of month]; FILTER ( Material; [Material] = sv ) );
        sv = "Forecasted"; [forecast];
        sv = "Material - Forecasted"; [stock end of month] - [forecast];
        BLANK ()
    )

 

 

 

Next time you are in need of assistance from the forum, please show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Thank you so much, I had never done this myself.

I'll take your advice and post sample data and my pbix file next time.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors