Forum Discussion

sharmon9000's avatar
sharmon9000
Helper I
8 years ago
Solved

Current Week Companies List on Billing Report

Currently we load our billing report (an excel sheet) into PowerBI.  We only have a small selection of clients that get billed, not every client has a bill every week. The report currently, now has tabs for Latest Week and Year to Date.  Recently I was requested to have a filter that would only show the Latest Week clients on the the Year to Date. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The highlighted example on the right, is how the output is needed in PowerBI.  I just cant figure out how to flag the current week companies, and then use that as a filter for the YearToDate matrix.  The filter on the Year to Date would need to be able to be turned on an off.  I could manually add a flag in the excel sheet, but was trying to do only in power BI and not have to edit the excel sheet.

 

Any help would be greatly appreciated.

 

-thanks

Need Help
  • smpa01's avatar
    smpa01
    8 years ago
    Latest Week- Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "Latest Week. AMT"}})
in
    #"Renamed Columns"
    YTD Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "YTD. AMT"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Company "},Table1,{"Company "},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Latest Week. AMT"}, {"Latest Week. AMT"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1", each ([Latest Week. AMT] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Latest Week. AMT"})
in
    #"Removed Columns"

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi sharmon9000

     

    Create a relationship between Year To Date and Latest Week Tables

     

    Then you can use the

     

    Company column from Lastest Week Table

    and

    Amount column from Year to Date Table

     

    to get desired results

    • sharmon9000's avatar
      sharmon9000
      Helper I
      8 years ago

      Zubair, thanks for the quick response.  How would I split the table?  Currently I just have a column that checks to see if the invoice date/week is the same as the current week.  I then use that in a filter for the Current Week tab.  The Year to Date tab is the same visual not including the Current week filter, so it show everything. Sorry for the confusion about being multiple tables.

       

       

      thanks

      • smpa01's avatar
        smpa01
        Community Champion
        8 years ago
        Latest Week- Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "Latest Week. AMT"}})
in
    #"Renamed Columns"
        YTD Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ", type text}, {"AMT", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"AMT", "YTD. AMT"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Company "},Table1,{"Company "},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Latest Week. AMT"}, {"Latest Week. AMT"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Table1", each ([Latest Week. AMT] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Latest Week. AMT"})
in
    #"Removed Columns"
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    As long as there is a Date column in your Base Data (from where both the reports are being geneated), your desired result should be attainable.  Share the link from where i can download your file.