Solved: Cumulative order lines in last 20 working days

AAMARILLO · ‎11-02-2020

I would like to show in my pivot table, the cumulative order lines inserted in last 20 working days.

I mean, if my date base is Nov 1st, the formula should show the qty of order inserted from Oct 5th till Nov 1st.

If my date base is Oct 20th, the formula should show the qty of order inserted from Sep 23th to Oct 20th.

Allways count the qty of order inserted in the last 20 working date.

Thank you for your support.

edhans · ‎11-02-2020

You would need a "Working Day" field in a date table to do this effectively, and that may not always be Monday-Friday if holidays are involved. But assuming you have such a field and it is true/false, and then create an index off of those true fields. I have this in Power query which has generated such an index.

You can see that the weekends (non-working days in my example)_ have the same index as the friday before them. This would be in your date table.

The M code to do this is here

let
    Source = {Number.From(#date(2020,1,1))..Number.From(#date(2020,12,31))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Added IsWorkday" = Table.AddColumn(#"Changed Type", "IsWorkDay", each if Date.DayOfWeek([Date]) > 0 and Date.DayOfWeek([Date]) < 6 then true else false, type logical),
    #"Filtered Rows" = Table.SelectRows(#"Added IsWorkday", each ([IsWorkDay] = true)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added IsWorkday", {"Date"}, #"Added Index", {"Date"}, "Added Index", JoinKind.LeftOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Index"}, {"Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index",{{"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Index"})
in
    #"Filled Down"

You would then need to get the current date's index, and subtract 20 from it to get the previous 20 working days. So something like this:

20 Workday Cumulative Sales = 
VAR varCurrentDateIndex = 
    MAXX(
        RELATEDTABLE('Date'),
        'Date'[Index]
    )
VAR varPastDate = varCurrentDateIndex - 20
VAR Result = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALL('Date'),
            'Date'[Index] <= varCurrentDateIndex
                && 'Date'[Index] >= varPastDate
        )
    )
RETURN
    IF(
        ISBLANK(SUM('Table'[Sales])),
        BLANK(),
        Result
    )

It returns this table visual

The first column is my source data, so very very minimal here, but you can see how it works.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need more help, please provide some sample data

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

CNENFRNL · ‎11-02-2020

Hi, @AAMARILLO , my solution is a bit tricky given the number in question, say 20, is special, assuming that all other holidays are omitted except weekends. Every consecutive 28 days surely contain 4X5 working days. Thus this measure fits in your scenario,

Last 20 Working Days =
VAR __period =
    DATESINPERIOD ( Calendar[Date], MAX ( 'Table'[Date Base] ), -28, DAY )
VAR __workingdays =
    FILTER ( __period, WEEKDAY ( Calendar[Date], 2 ) < 6 )
RETURN
    SUMX ( __workingdays, CALCULATE ( SUM ( Order[Qty] ) ) )

A date table (Calendar table) is necessary.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!