Forum Discussion

vijay273162's avatar
vijay273162
Helper III
4 years ago

Running Day calculation

Hi All,

I'm creating a table matrix, by taking columns from my calendar table as below.

 

for Running Days of Quarter ( exlcuding Weekend ) I used below calculated column dax code to get reverse order of running days.

DESC - Running days_Quarter_(Excl_Wknd) =
COUNTROWS(
FILTER(
ADDCOLUMNS(CALENDAR(Period[Date],Period[End date of Quarter]),
"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),
[WorkDay] =1)
)
)

 

Then I use this column to compare the data in both Q1 FY22 & Q1 FY23. I get result like below table in table matrix.

Now, I need to bring Q1 FY22 data into blank rows of Q1 FY23, 

 

How to do it ?, If I use SAMEPERIODLASTYEAR ( data's not matching as we sum ( Sat + Sun into Mon ) data.

Is there a eactly copy the data of Q1 FY22 based on Running Days into Q1 FY23

 

For Ex:

 

58th Running Day in Q1 FY22 = 145, which I should get it for the same 58th Day in Q1 FY23

 

Running Days of Quarter (excl. Weekends)Q1 FY22Q1 FY23
65100134
6412325
63435567
62542345
61562345
6034123
59215123
58145 
57345 
56145 
55456 
54658 

 

Need Help

2 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    It is best practice to use a Calendar table rather than attempt to write dax date logic.
    Build it and test it once and use always ...

    Click here to view several free calendar training videos 

     

    In the calendar have

    • date ( a contiguous list)  
    • working day indicator ( 1 for working / o for non-workimg0
    • Fiscal Quarter (eg Q1 FY22, Q2 FY22) 
    • Fiscal year offset (eg 0, -1, -2)

     

    Create 1:m relationship from calendar date to your fact table date.

     

    You can then sum, slice, filter or group MTD, YTF, YE, LY etc.

     

    For example ...

     

    SalesThisYear =CALCULATE(SUM(Facts[Amount], Calendar[FiscalYearOffset = 0)
    SalesLastYear =CALCULATE(SUM(Facts[Amount], Calendar[FiscalYearOffset = -1)

     

    It may frustratingly take you longer to build the calendar than develop a dax solution on this occasion but a calendar is reusable and will save you time with other date measures. ğŸ˜€

     

  • vijay273162's avatar
    vijay273162
    Helper III
    4 years ago

    Yes, I do have dedicated Calendar Table.

     

    Hope, I haven't clearly explained. 

    Refer the below Simplified example.

     

    So, I need to go back to PREVIOUS YEAR SAME QUARTER and based on Running day number, need to get the Total Sales value.

     

    If I use SAME PERIODLAST YEAR, it only takes "total sales" for that date, this will not consider the logic of summing Weekend data ith Monday.

     

    Please tell me, how to use Running Day number columns to get previous year "Total sales" data