Dynamic text value not showing the correct past few weeks

[BBIUser]

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?

Appreciate your help!

[TomMartens · Accepted solution]

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

[amitchandak · Accepted solution]

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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

Connect on Linkedin

[v-kelly-msft]

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

[BBIUser]

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?

[v-kelly-msft · Accepted solution]

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

[BBIUser]

Thank You @v-kelly-msft 

These solutions worked too @amitchandak @TomMartens Thank You!

[TomMartens · Accepted solution]

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

[amitchandak]

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

[BBIUser]

@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.

[amitchandak · Accepted solution]

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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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

Connect on Linkedin