cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

Dynamic text value not showing the correct past few weeks

Hello Community,

I have column 'Year_Week' with records such as 2019-38, 2019-39, .............2019-53. This works well with the following DAX expressions 1. calculated column 2. Measure, if it was for Year 2019.

``````1. V_Weeks = RIGHT('Table1'[YEAR_WEEK],2)
2. WeeksCalM = MAX('Table1'[V_Weeks])-MIN('Table1'[V_Weeks])``````

Logic is: Lets say the starting week is 2019-15 and current week running is 2018-29, the result set should show 14. i.e., "past 14 weeks".

Issue: Refer the table below. When the Year 2020 started, it is not showing the correct value. As of this week, it should show 19 weeks and increment there after dynamically.

How do I make this work?

3 ACCEPTED SOLUTIONS
Super User

Hey @BBIUser ,

you have to create a column that represents an index number for your calendar week.

The first week in your timeframe becomes the index 1, the last week (assuming your timeframe spans 105 weeks) then is represented by the integer value 105.

Now it's quite easy to calculate the duration.

You might consider to create a separate table that just contains unique values of the column v_WEEKS. Then create a relationship between both tables with new week table on the one side and your existing table on the many side.
A calculated column that represents the running week index can be created using this DAX statement:

``````running week index =
var _thisweek = 'Table (2)'[V_WEEKS]
return
CALCULATE(
DISTINCTCOUNT('Table (2)'[V_WEEKS])
, FILTER('Table (2)' , 'Table (2)'[V_WEEKS] < _thisweek
)
) + 1``````

The week table will now look like this:

Hopefully, this provides you with some new ideas to tackle your challenge.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

One of the way is to go rolling. But that need date table and some additional filter in the formula given below for week start and end.

``````Rolling 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-7*14,MONTH))
Week Start date = DATEADD('Date'[Date],-1*WEEKDAY('Date'[Date])+1,DAY)
Week End date = DATEADD('Date'[Date],7-1*WEEKDAY('Date'[Date]),DAY)
``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Community Support

Hi @BBIUser ,

Answers are as below:

1 & 2. As the weeknum is counted from 1 in 2019,which I have writen in the measure,but in 2020,I counted from 0 in my previous .pbix file,but if you wanna show only 19 weeks, then the weeknum should be counted from 0,and the measure should be corrected as below:

``````Measure =
VAR a =
CALCULATE (
MAX ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2019" )
)
VAR b =
CALCULATE (
MIN ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2019" )
)
VAR c =
CALCULATE (
MAX ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2020" )
)
VAR d =
CALCULATE (
MIN ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2020" )
)
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Table'[Weeknum] ) >= b
&& SELECTEDVALUE ( 'Table'[Weeknum] ) < a + 1, SELECTEDVALUE ( 'Table'[Weeknum] ) - b ,
SELECTEDVALUE ( 'Table'[Weeknum] ) >= d
&& SELECTEDVALUE ( 'Table'[Weeknum] ) < c + 1, SELECTEDVALUE ( 'Table'[Weeknum] ) - d + ( a - b ) + 1
)``````

And you will see:

3. When using a card visual with measure,if it shows blank,it means that it doesnt have a value,you only need to select a value in a certain row as below:

Here is my related .pbix file if you need.

Best Regards,
Kelly

8 REPLIES 8
Community Support

Hi @BBIUser ,

Create 2 calculated columns as below:

``Weeknum = RIGHT('Table'[Year of Week],2)``
``year = LEFT('Table'[Year of Week],4)``

Then convert the the column "weeknum" to "whole number "type,and you will see :

Then create a measure as below:

``````Measure =
VAR a =
CALCULATE (
MAX ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2019" )
)
VAR b =
CALCULATE (
MIN ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2019" )
)
VAR c =
CALCULATE (
MAX ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2020" )
)
VAR d =
CALCULATE (
MIN ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2020" )
)
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Table'[Weeknum] ) >= b
&& SELECTEDVALUE ( 'Table'[Weeknum] ) < a + 1, SELECTEDVALUE ( 'Table'[Weeknum] ) - b + 1,
SELECTEDVALUE ( 'Table'[Weeknum] ) >= d
&& SELECTEDVALUE ( 'Table'[Weeknum] ) < c + 1, SELECTEDVALUE ( 'Table'[Weeknum] ) - d + ( a - b ) + 1
)``````

At last you will see :

For the related .pbix file,pls click here.

Best Regards,
Kelly

Helper IV

I am trying all 3 solutions.

@v-kelly-msft But you're solution is very close to what I was trying as an alternative.

I have few questions based on your solution and I am very close to get the correct solution.

1) Why is the record 16 repeating in 'Measure' column?

2) In the dynamic text, I need to display only 19 Weeks in a card, even though the table shows 20 weeks (In 'Measure' column)?

When it is 21st week, the Card should show 20 Weeks.

3) If I use the measure field in the card it displays (Blank). What is the reason?

Community Support

Hi @BBIUser ,

Answers are as below:

1 & 2. As the weeknum is counted from 1 in 2019,which I have writen in the measure,but in 2020,I counted from 0 in my previous .pbix file,but if you wanna show only 19 weeks, then the weeknum should be counted from 0,and the measure should be corrected as below:

``````Measure =
VAR a =
CALCULATE (
MAX ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2019" )
)
VAR b =
CALCULATE (
MIN ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2019" )
)
VAR c =
CALCULATE (
MAX ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2020" )
)
VAR d =
CALCULATE (
MIN ( 'Table'[Weeknum] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = "2020" )
)
RETURN
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Table'[Weeknum] ) >= b
&& SELECTEDVALUE ( 'Table'[Weeknum] ) < a + 1, SELECTEDVALUE ( 'Table'[Weeknum] ) - b ,
SELECTEDVALUE ( 'Table'[Weeknum] ) >= d
&& SELECTEDVALUE ( 'Table'[Weeknum] ) < c + 1, SELECTEDVALUE ( 'Table'[Weeknum] ) - d + ( a - b ) + 1
)``````

And you will see:

3. When using a card visual with measure,if it shows blank,it means that it doesnt have a value,you only need to select a value in a certain row as below:

Here is my related .pbix file if you need.

Best Regards,
Kelly

Helper IV

Thank You @v-kelly-msft

These solutions worked too @amitchandak @TomMartens Thank You!

Super User

Hey @BBIUser ,

you have to create a column that represents an index number for your calendar week.

The first week in your timeframe becomes the index 1, the last week (assuming your timeframe spans 105 weeks) then is represented by the integer value 105.

Now it's quite easy to calculate the duration.

You might consider to create a separate table that just contains unique values of the column v_WEEKS. Then create a relationship between both tables with new week table on the one side and your existing table on the many side.
A calculated column that represents the running week index can be created using this DAX statement:

``````running week index =
var _thisweek = 'Table (2)'[V_WEEKS]
return
CALCULATE(
DISTINCTCOUNT('Table (2)'[V_WEEKS])
, FILTER('Table (2)' , 'Table (2)'[V_WEEKS] < _thisweek
)
) + 1``````

The week table will now look like this:

Hopefully, this provides you with some new ideas to tackle your challenge.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

You are doing above to get what. last 19 week of data?

Helper IV

@amitchandakThanks for the reply. Correct.

Starting from 38th week until this week 2020-04 = 19 weeks. For the next week, it should show 20 weeks.

Super User

One of the way is to go rolling. But that need date table and some additional filter in the formula given below for week start and end.

``````Rolling 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-7*14,MONTH))
Week Start date = DATEADD('Date'[Date],-1*WEEKDAY('Date'[Date])+1,DAY)
Week End date = DATEADD('Date'[Date],7-1*WEEKDAY('Date'[Date]),DAY)
``````

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.