Current Week Companies List on Billing Report

[sharmon9000]

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

[Ashish_Mathur]

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.

[Zubair_Muhammad]

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]

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

[v-huizhn-msft]

Hi @sharmon9000,

Whta's your plan to split the table? We can split a column by Delimiter, please follow the steps in this article(Althrough it's used in excel, all steps are same in Power Query of Power BI). 

In addition, you create a slicer for the Current Week tab? Could you please share more details as @Ashish_Mathur said? So that we can post solution which is close to your requirement.

Best Regards,
Angelia

[smpa01 · Accepted solution]

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"