DAX for You!

[monaraya]

Scenario:

Fetch the count of products in a given date range between the Start and End Date Ranges.

Tables Used:

• Date table with the dates starting from 1/1/2020 to 3/26/2020
• Product table “ProdDateRange” with Start Date and End Date.

Product	Start	End
A	1/1/2020	1/5/2020
B	1/9/2020	1/12/2020
C	1/4/2020	1/8/2020
D	1/5/2020	1/15/2020

1. I have taken Sample date table which has dates between January to March and used the Excel connector to fetch the data into the Power BI Desktop.
2. Created a Sample table “ProdDateRange” which consists of Product, Start and End columns. Start and End are Date columns.
3. Go to query editor via Transform Data option.
4. Get the date ranges for each Product in a single column. To do that, click on “columns from examples” in query editor to get the Date difference. Post that use the M query “List.Dates([Start], [DateDiff]+1, #duration(1,0,0,0))” to create a custom column. This column consists of the list of date ranges for each product.
5. Click on “Expand to new rows” to get the date range for each product in a single Column “DateRange”. Please find the transformed table below:
6. Apply the changes.
7. Now, we will create a relationship between dates column in Date table and DateRange from “ProdDateRange” table.
8. Create a Calculated Column “Deliveries in Progress” in Date table to get the number of deliveries which are in progress for a given date.

Deliveries in progress = IF(ISBLANK(COUNTROWS(RELATEDTABLE('ProdDateRange'))),0,COUNTROWS(RELATEDTABLE('ProdDateRange')))

Complete M Query:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyADNNIcxYnWglJ7CAJULO0Agh6QwWMUFIWiDkXJBNAmuEmRoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Start = _t, End = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Start", type date}, {"End", type date}}),

    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each Duration.Days([End] - [Start]), Int64.Type),

    #"Renamed Columns" = Table.RenameColumns(#"Inserted Date Subtraction",{{"Subtraction", "DateDiff"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Dates([Start], [DateDiff]+1, #duration(1,0,0,0))),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),

    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom", "DateRange"}})

in

    #"Renamed Columns1"

9. To display the number of product in the selected date range, add slicer with the dates column from Date table and in a table visual add the fields “Dates” and “Deliveries in Progress”.

10. Now we can see the required data based on the slicer selection.

Author: Kirthi Korukonda

Reviewer: Mounika Narayana Reddy