Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Calendar Formula for Dates Between Last 2 Week Ending Periods

Our payroll runs biweekly. I would like to have a filter or week calculation that will show the last 14 day of the payroll week (Payroll closes on Saturdays), so, for example,  So for example, since today is 4/25, I want all the dates from 4/12 - 4/20 (last 2 previous week endings).  So I would like to add a calculation in my DIMdate that can calculate my in two weeks based on the last weekday of the past 2 weeks.

Need Help

5 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi Anonymous 

     

    you can try something like this:

    previous2WeeksFlag =
VAR _datediff =
    DATEDIFF ( dimDate[Date]; TODAY () - WEEKDAY ( TODAY (); 1 ); DAY )
RETURN
    IF ( _datediff < 14 && _datediff >= 0; 1; 0 )

     
    Cheers,
    Sturla 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Please refer to my file for week wise analytic it uses week rank to deal with it

    https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

     

    like

    This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

    2nd Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-2))

     

    2 rolloing  Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-2)

    && 'Date'[Week Rank]<=max('Date'[Week Rank]))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey Guys. Thanks for the help. See the image below. What I basically need is create a filter column every 2 weeks (payroll period from Sunday to Saturday). For example, If I filter end week  04/25, this will show all dates and hours worked from 04/12 - 04/25) and keep the cadence for future dates and previous dates, instead of having the user select both weeks.

     

    amitchandak sturlaws