Forum Discussion

manishpadmajan's avatar
manishpadmajan
Regular Visitor
2 years ago
Solved

Highlight every week's total in table

Hi Team,

 

I have table column with dates of a month and sales figures against each of the dates.

There are two requirements with this data

1) Get the total sales for a week. After that total has to reset and get calculated for the next week.

2) The total for every week should be highlighted in the table control showing the data.

 

For #1, I am doing a cumulative total using sales data for dates in a week and then it resets for the next week and starts the calculation again for the new week as shown below

For this I have a measure in place which does the weekly calculation.

WeeklyTotal =

var CurrentDate = Max(salesdata[Date])
var CurrentWeek = Max(salesdata[Week])
Return
CALCULATE(sum(salesdata[Sales]),salesdata[Date] <=CurrentDate, salesdata[Week] = CurrentWeek)
 
Here Week is another calculated column.
 
For #2,  to highlight weeklytotal values (in orange as shown in screen shot above). I have tried the ranking approach. But the issue I am facing is to reset the ranking every week. I am not sure how this can be done.
Any inputs or better approach to address this problem will be really helpful.
 
Thank you.
 
with rgds,
Manish

 

 

Need Help
  • Dangar332's avatar
    Dangar332
    2 years ago

    hi, manishpadmajan 

     

    use below measure for conditional formatting

     

     

    Measure = 
var a = CALCULATE(MAX('Table (2)'[weekly total]),ALLEXCEPT('Table (2)','Table (2)'[week]))
return
IF(MIN('Table (2)'[weekly total])=a,"#FFFF00")

     

     

     

     

     

     

    1. click on weekly total coluymn 

    and follo step in iamge

     

    change format style to field value

     

    3. click on table name where measure is present and choose that measure and click on OK

     

     

    download .pbix file click  HERE 

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi manishpadmajan 

     

    For your question, you've completed the first requirement, and here I'm helping you with the second:

     

    Here's some dummy data, based on what you provided, I also created the "week" calculated column.

     

    You want to highlight the weekly total, you can create a measure to query the last day of the week and makes it as “1”.

     

    IsLastDayOfWeek = 
var max_date = CALCULATE(MAX('Table'[Date]), FILTER(ALL('Table'), 'Table'[Week] = MAX('Table'[Week]) ))
var _date = SELECTEDVALUE('Table'[Date])
return IF(_date = max_date, 1, 0)

     

     

    Set the highlight for “WeeklyTotal” according to the “IsLastDayOfWeek”

     

     

    Here is the result

     

     

    Regards,

    Nono Chen

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

     

     

2 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    hi, manishpadmajan 

     

    use below measure for conditional formatting

     

     

    Measure = 
var a = CALCULATE(MAX('Table (2)'[weekly total]),ALLEXCEPT('Table (2)','Table (2)'[week]))
return
IF(MIN('Table (2)'[weekly total])=a,"#FFFF00")

     

     

     

     

     

     

    1. click on weekly total coluymn 

    and follo step in iamge

     

    change format style to field value

     

    3. click on table name where measure is present and choose that measure and click on OK

     

     

    download .pbix file click  HERE 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi manishpadmajan 

     

    For your question, you've completed the first requirement, and here I'm helping you with the second:

     

    Here's some dummy data, based on what you provided, I also created the "week" calculated column.

     

    You want to highlight the weekly total, you can create a measure to query the last day of the week and makes it as “1”.

     

    IsLastDayOfWeek = 
var max_date = CALCULATE(MAX('Table'[Date]), FILTER(ALL('Table'), 'Table'[Week] = MAX('Table'[Week]) ))
var _date = SELECTEDVALUE('Table'[Date])
return IF(_date = max_date, 1, 0)

     

     

    Set the highlight for “WeeklyTotal” according to the “IsLastDayOfWeek”

     

     

    Here is the result

     

     

    Regards,

    Nono Chen

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