Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Limit week to current week

Hello All,

 

Hope you are fine.

 

I am trying to limit my table to the current week but unfortunately, it is not working:

 

My sales figures are being retrieved from the server on a daily basis, however, my budget column is from an excel file which I loaded.

 

I tried the below but it does not seem to work:

 

Current_wk_budget =
VAR _CurrentDate = SELECTEDVALUE( 'Calendar'[Date].[Date] )
VAR _CurrentWeekYear = SELECTEDVALUE( 'Calendar'[YYYY-WK] )

RETURN
CALCULATE(
    BUDGET[Budget_1],
    FILTER(
        ALL( 'Calendar' ),
        'Calendar'[YYYY-WK] = _CurrentWeekYear &&
        'Calendar'[Date].[Date] <= _CurrentDate
    )
)

 

OR

Weekly_sales =
var _maxYear = Calculate(MAX('Calendar'[Year]), All(BUDGET))
var _maxWeek = Calculate(MAX('Calendar'[YYYY-WK]), ALL('Calendar'), 'Calendar'[Year] = _maxYear)
return
IF(MAX('Calendar'[YYYY-WK])<= _maxWeek, Sum(BUDGET[Daily Budget]),BLANK())
 
Any idea on how I can make this work?
 
Many Thanks.
 
Kind Regards,
 
Hasvine

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    You may try this measure for the budget of the current week:

    Current_wk_budget =
VAR _CurrentDate = TODAY ()
VAR _CurrentWeekYear = CALCULATE ( MAX ( 'Calendar'[YYYY-WK] ), 'Calendar'[Date] = _CurrentDate )
RETURN
    CALCULATE (
        SUM ( BUDGET[Budget_1] ),
        'Calendar'[YYYY-WK] = _CurrentWeekYear,
        'Calendar'[Date] <= _CurrentDate
    )

     For weekly sales, your measure is also calculating sum from BUDGET table instead of any Sales table. Is the daily sales data in the same BUDGET table? 

     

    If this doesn't help, can you please share some sample data as well as expected result in table format? Does your "current week" need to be compared with today's date?  For example, as the current week is 202304, do you want to show only this week's data in the table or do you want to show data of all weeks before or on this week? Sample data is important to help us understand what you have and try to provide a possible solution quickly. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    You may try this measure for the budget of the current week:

    Current_wk_budget =
VAR _CurrentDate = TODAY ()
VAR _CurrentWeekYear = CALCULATE ( MAX ( 'Calendar'[YYYY-WK] ), 'Calendar'[Date] = _CurrentDate )
RETURN
    CALCULATE (
        SUM ( BUDGET[Budget_1] ),
        'Calendar'[YYYY-WK] = _CurrentWeekYear,
        'Calendar'[Date] <= _CurrentDate
    )

     For weekly sales, your measure is also calculating sum from BUDGET table instead of any Sales table. Is the daily sales data in the same BUDGET table? 

     

    If this doesn't help, can you please share some sample data as well as expected result in table format? Does your "current week" need to be compared with today's date?  For example, as the current week is 202304, do you want to show only this week's data in the table or do you want to show data of all weeks before or on this week? Sample data is important to help us understand what you have and try to provide a possible solution quickly. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.