Forum Discussion

cgaralde's avatar
cgaralde
Frequent Visitor
3 years ago
Solved

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

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.  R...
Need Help
Tutorial Requests
  • cgaralde's avatar
    cgaralde
    3 years ago

    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. 🙂

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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-Last-Week/ba-p/1051123
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's avatar
cgaralde
Frequent Visitor
3 years ago

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. 🙂