Creating Measures using Time Intelligence Filters

[Kenta]

Hi,

Link to PBIX and supporting files: 

https://www.dropbox.com/sh/rhd62hy796zw9g6/AAD7SLp0WuTACIZ38bP_BxyZa?dl=0 https://www.dropbox.com/sh/rhd62hy796zw9g6/AAD7SLp0WuTACIZ38bP_BxyZa?dl=0 

I'm looking for some help creating measures that automatically filter to a specific timeframe depending on the period selected with a slicer.

I've put together the following example to keep things simple:

We are a distributor of Home/Hardware products and have a partnership with our dealers through which they send us weekly sell thru data.

Every Monday, I receive a basic spreadsheet from each dealer that includes:

- Week Number (aligns with my company fiscal calendar)

- Product Code

- Units Sold

- Current Units On Hand

I've created relationships between the dealer files, a Calendar Table, Account Master, and Product Master list.

I've put together a dashboard that has a date slicer as well as a basic table. 

In this table I currently only have a measure that calculates Total Unit Sales by Product Name, I would however like to add the following measures:

- Ending On Hand Inventory, this metric would have to only sum inventory for the Last Week of the period chosen through the slicer.

Example: If I choose January WK3 through February WK1 with the slicer, the measure named "Total Unit Sales" will SUM unit sales for the 3 selected weeks; however I would like the "Ending On Hand Inventory" measure to only show inventory levels for February WK1.

- Year To Date Sales, this measure would SUM unit sales from the begining of the year up until the week selected through the Slicer.

- 3 Week Sales Average, this measure would average unit sales for the selected week + the 2 prior weeks of sales, once again depending on the period selected with the Slicer.

Any help is extermely appreciated!

Thanks,

[Kenta]

Hi Ashish,

There isn't an actual date column in the dealer files, instead we have a "Fiscal Week Number" column. That's what I use to create a relationship between the Dealer and Calendar table.

I had opted to use Week Numbers as we do not receive daily sales reports, only weekly.

[Ashish_Mathur]

Hi,

Do the dealer files have an actual/proper date column?  If no, then you must so that you can make use of the Date/Time Intelligence functions.  Also, if you select January WK3 through February WK1 in the slicer, how will you calculate 3 Week Sales Average?  If you have a proper date column in the dealer files, then share the download link of your PBI file.