cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Functionality available in Excel but not in Power BI

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
Highlighted
Microsoft
Microsoft

Re: Functionality available in Excel but not in Power BI

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

Re: Functionality available in Excel but not in Power BI

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

 

 

Highlighted
Microsoft
Microsoft

Re: Functionality available in Excel but not in Power BI

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

Re: Functionality available in Excel but not in Power BI

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.

Highlighted
Anonymous
Not applicable

Re: Functionality available in Excel but not in Power BI

Hi Dale,

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

Highlighted
Anonymous
Not applicable

Re: Functionality available in Excel but not in Power BI

Hi Dale,

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

Highlighted
Microsoft
Microsoft

Re: Functionality available in Excel but not in Power BI

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors