Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Plotting measure against every Monday in a year

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])))

Data.JPG
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.

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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])

Capture8.JPGCapture9.JPG

Create a measure

Measure = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[order date]<SELECTEDVALUE('date'[monday])&&'Table'[release date]>=SELECTEDVALUE('date'[monday])))
Capture7.JPG
 
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.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

If you'd like to create a chart as below

Capture10.JPG

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

Capture12.JPG

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))

Capture11.JPG

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])

Capture13.JPG

 

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
Community Support
Community Support

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])

Capture8.JPGCapture9.JPG

Create a measure

Measure = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[order date]<SELECTEDVALUE('date'[monday])&&'Table'[release date]>=SELECTEDVALUE('date'[monday])))
Capture7.JPG
 
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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.