Plotting measure against every Monday in a year

[Anonymous]

Hey Power BI Experts,
I am having a table with 2 date columns- ORDER CREATE DATE and Eng Release DATE.
I do not have any date table or another date here. My requirement is to build a measure which gives me a backlog using below formula and plot it across a date which displays all Mondays of an year:

Backlog= Calculate(countrows()), ORDER CREATE DATE < selected monday, Eng Release DATE>=selected monday

I created a date table using date ordered column, and a week ending column based to calculate Monday , but when I plot it against the measure, it shows me the value for only that one week, not for all:

Measure Backlog = CALCULATE(COUNTROWS('Table BI'),FILTER('Table BI','Table BI'[Date Order Entered]<RELATED('date'[Week Ending])),FILTER('Table BI','BI'[Eng Release Date]>RELATED('date'[Week Ending])))

So, for Weekending 4/9/2018, count should be 1
4/16/2018, count should be 2
4/23/2018, count should be 2.

Any help is appreciated, thanks in advance.

[v-juanli-msft]

Hi @Anonymous 

If you'd like to create a chart as below

In edit queries, add a index column, unpivot columns for "order date" and "release date",

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcvJCQAwCETRXuYcMC5ZehH7byMbwdsfH7qDUWBkJJXnzkb6MopDrrEkWqJeFE3kmmr7csb56L8bDUQs", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [lane = _t, #"order date" = _t, #"release date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"lane", Int64.Type}, {"order date", type date}, {"release date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"lane", "Index"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Attribute_cate"}, {"Value", "Value_date"}})
in
    #"Renamed Columns"

Close&&apply, create a new table

date 2 = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2),"weekday",WEEKDAY([Date],2))

Add columns in date 2

monday = IF([weekday]=1,[Date])

order date condition = CALCULATE(COUNT(Table_copy[Index]),FILTER(ALL(Table_copy),Table_copy[Attribute_cate]="order date"&&Table_copy[Value_date]<'date 2'[Date]))

order date condition = CALCULATE(COUNT(Table_copy[Index]),FILTER(ALL(Table_copy),Table_copy[Attribute_cate]="order date"&&Table_copy[Value_date]<'date 2'[Date]))

final = MIN([order date condition],[release date condition])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a date table

date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2),"weekday",WEEKDAY([Date],2))

Create a calculated column

monday = IF([weekday]=1,[Date])

Create a measure

Measure = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[order date]<SELECTEDVALUE('date'[monday])&&'Table'[release date]>=SELECTEDVALUE('date'[monday])))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft  ,

Thank you so much for the solution, it worked 🙂

In fact, I created the week ending monday in the main table itself using create date as the date column and created the similar measure and it worked there as well !

Regards,

Sakshi