How to get the dynamically change in power bi card by current week wise.

[hb0135]

Hello,
I have create a card for plan and actual delivery in power bi but for current week i need to select in filters in each week for that i need dynamic card for currrent week for below data i.e in excel till week 52.

BR
HB0135

[amitchandak · Accepted solution]

@hb0135 , You can have date table with week rank, based on the selection

Have these new columns in the Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Or based on today you can have

This Week Today =
var _min = today() -WEEKDAY(today() ,2) +1 //Monday week start
var _max = _min +6
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Last Week Today =
var _min = today() -WEEKDAY(today() ,2) -6 //Monday week start
var _max = _min +6
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

In case you have active inactive join you can first create a measure with userelationship

calculate( SUM('Table'[Amount]),USERELATIONSHIP ('Table'[Actual Delivery Date], 'Date'[Date]))

And then use week code

[Ritaf1983 · Accepted solution]

Hi @hb0135 
If you don't have a date table in your model you can use a dynamic filter - top n to take the last week
+ Filter out zeros

result:

pbix is attached

Note that this is not the best practice  .
My recommend is to learn about the data table and use it in the model.
For more information please refer :

https://www.youtube.com/watch?v=LfKm3ATibpE

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[hb0135]

Hello,

Thanks for help

[Ritaf1983]

You're welcome 🙂

[amitchandak · Accepted solution]

@hb0135 , You can have date table with week rank, based on the selection

Have these new columns in the Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Or based on today you can have

This Week Today =
var _min = today() -WEEKDAY(today() ,2) +1 //Monday week start
var _max = _min +6
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Last Week Today =
var _min = today() -WEEKDAY(today() ,2) -6 //Monday week start
var _max = _min +6
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

In case you have active inactive join you can first create a measure with userelationship

calculate( SUM('Table'[Amount]),USERELATIONSHIP ('Table'[Actual Delivery Date], 'Date'[Date]))

And then use week code

[hb0135]

Hello,

Thanks for help