Filter Quantity on Week-to-date based on Slicer selection

[cgaralde]

Hi,

I am working on a personal project for work that requires a user to select a Week Number (W1 2023) and based on this slicer, it should show the current stock as of the last day of W1 2023.  Right now, if I just use this without DAX I get the stock total based on the starting date to ending date, which is obviously not the way stock works. 🙂

Here is the premise:

I have the following simple model. On the Trading_Calendar table I have a column containing the start date and end date of a particular week.

If I do the following:

CALCULATE(

SUM(Fact_Stock_w_Cost[Qty]),

Fact_Stock_w_Cost[Posting Date] <= MAX(Dim_Trading_Calendar[End_of_Week])

)

CALCULATE(

SUM(Fact_Stock_w_Cost[Qty]),

FILTER(

Fact_Stock_w_Cost,

Fact_Stock_w_Cost[Posting Date] <= MAX(Dim_Trading_Calendar[End_of_Week])

)

)

 

Both just give me the same as the SUM(Fact_Stock_w_Cost[Qty]).

SUMX(Fact_Stock_w_Cost,Fact_Stock_w_Cost[Posting Date] <= [EndOfWeek])

Gives me an error stating that it cannot evaluate BOOLEAN.

If anyone would be able to help me with the DAX formula with an explanation of the syntax it would be greatly appreciated. 🙂

[amitchandak]

@cgaralde , refer if my week rank way

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

You can also consider window

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[cgaralde · Accepted solution]

@amitchandak, thanks for the links that you provided.  It definitely pointed me to the right direction.

I am able to solve my requirements by doing the following:

I added a CURRENT DAY OFFSET COLUMN in power query using the following:

Number.From([Date]) - Number.From(Date.From( DateTime.FixedLocalNow()))

This gives me an integer value showing this:

Based on this I made the following DAX statement:

SOH =

VAR currOffset = MAX(Dim_Trading_Calendar[Curr Day Offset])

//Takes the maximum offset of the current selection in this case

// -11 as my slicer selection was W8 2023

VAR WTDSelection = CALCULATE(

SUM(Fact_Stock_w_Cost[Qty]),

//I wanted to sum the Qty Column from this table

ALL(Dim_Trading_Calendar),

//I wanted to clear all filters from my calendar table, in this case current

//selection is Week 8 2023

Dim_Trading_Calendar[Curr Day Offset] <= currOffset

//I apply a new filter where the offset is less than or equal to -11.

//It gives me a filter of all columns prior and including the 26 FEB 2023

)

RETURN WTDSelection

I hope this helps someone else. 🙂