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
Anonymous
Not applicable

Functionality available in Excel but not in Power BI

Lets see if I can frame this and if anyone has idea to resolve it:

So I have a requirement where I have got the below excel from one of the user. The graph you see depends on the Month value selected in one the row(in pink). On selecting the month row I get the graph plotted accordingly. See the two example below( for month June and October). Based on month selection the value in YTD. You can see the value of YTD2 and FC2 and YTD+FC column value changes based on month selection.

excel1.JPGexcel2.JPGexcel3.JPGexcel4.JPG


The same functionality I am trying to achieve in POWER BI but I dont see a visuals. By the help of this forum I was able to create the tabular data as below( I hope i have done right calculation) but now stuck at showing it in visuals. Please provide your expertise on resolving this. I am attaching the excel file in case more clarification is required.excel5.JPG 

 
I dont see an option to attach excel file 🙂

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

I found a solution finally. Please refer to the following steps. You can try it out in this file.

1. Create a new table "Date" that has all the dates from old table. Don't establish relationship.

2. Create three new measures.

Initial forcast = sum([Initial FC **bleep**])
Series 2 =
IF (
    MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ),
    0,
    CALCULATE (
        SUM ( Table1[YTD] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) )
    )
        + CALCULATE (
            SUM ( Table1[Forcast] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] > MIN ( 'Date'[Date] )
                    && 'Table1'[Date] <= MIN ( 'Table1'[Date] )
            )
        )
)
YTD+FC Measure =
IF (
    MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ),
    CALCULATE (
        SUM ( Table1[YTD] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Table1'[Date] ) )
    ),
    CALCULATE (
        SUM ( Table1[YTD] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) )
    )
        + CALCULATE (
            SUM ( Table1[Forcast] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] <= MIN ( 'Table1'[Date] )
                    && 'Table1'[Date] > MIN ( 'Date'[Date] )
            )
        )
)

3. Create a slicer whose field is from Date table.

4. Create a Line chart.

Functionality_available_in_Excel_but_not_in_Power_BI

 

 

Best Regards,

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

You can upload the file to a cloud drive like OneDrive, Dropbox, and then share the link here that I can download from. The visuals could be Line chart and Slicer. Which is the expected result? It seems the data in the Power BI is different from the data in the Excel.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Dale,

Thanks for being such a great help.

Here is the link to the drop box. The expected result is the one we see on Excel sheet that on changing month , we get different values for YTD2 and YTD +FC and accordingly the graph is plotted. Yes the data values in Power Bi is different, I just wanted to achieve what is there in Excel, and I hope I have done the calculation as it is in Excel sheet

Link to Excel Sheet

 

Look to hear soon from you

 

 

Hi @Anonymous,

 

I imported the table "Table66" directly and created a Line chart. Is the one below what you want? You can check it out in this file.

Functionality_available_in_Excel_but_not_in_Power_BI2

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Dale,

Were you able to get anything on this? Can you suggest if its even possible in Power Bi or not. Thanks

Hi @Anonymous,

 

I found a solution finally. Please refer to the following steps. You can try it out in this file.

1. Create a new table "Date" that has all the dates from old table. Don't establish relationship.

2. Create three new measures.

Initial forcast = sum([Initial FC **bleep**])
Series 2 =
IF (
    MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ),
    0,
    CALCULATE (
        SUM ( Table1[YTD] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) )
    )
        + CALCULATE (
            SUM ( Table1[Forcast] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] > MIN ( 'Date'[Date] )
                    && 'Table1'[Date] <= MIN ( 'Table1'[Date] )
            )
        )
)
YTD+FC Measure =
IF (
    MIN ( 'Table1'[Date] ) <= MIN ( 'Date'[Date] ),
    CALCULATE (
        SUM ( Table1[YTD] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Table1'[Date] ) )
    ),
    CALCULATE (
        SUM ( Table1[YTD] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Date] <= MIN ( 'Date'[Date] ) )
    )
        + CALCULATE (
            SUM ( Table1[Forcast] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Date] <= MIN ( 'Table1'[Date] )
                    && 'Table1'[Date] > MIN ( 'Date'[Date] )
            )
        )
)

3. Create a slicer whose field is from Date table.

4. Create a Line chart.

Functionality_available_in_Excel_but_not_in_Power_BI

 

 

Best Regards,

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Dale,

Were you able to get anything on this? Can you suggest if its even possible in Power Bi or not. Thanks

Anonymous
Not applicable

Yes this is what I am looking for. But if you see in Excel, I have option to change the month in one of the row highlighted in pink just above the graph. Based on the month my line graph changes along with values in YTD+FC and FC2. I would like to do the same calculation for that in Power Bi and same visualization. 

So in short I have input as Month, Cumalative FC and Cumalative YTD. and have to plot and design as it is in Excel. Thanks.

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.