Weekly Data

[JamesBurke]

Hi all , 

I have the dashboard Above and i'm looking a filter of some sort that will show the Max week Usages on the Daily performance by default and when filtered by weekend will still remain the latest ( if there is no data it will show no data)

I have Max weeks usages when nothing is selected against the average - 

Max Week Usages = 
Var MaxMonthValue = 
    CALCULATE(
        MAX('Date'[Year Week new]),
        FILTER('Date',[UsagesCount] >= 1),
        ALL('Date'))
        
RETURN 
CALCULATE(
    [Total Kwh],
    FILTER( 
        ALL('Date'),
        'Date'[Year Week new] = MaxMonthValue
    )

UsagesCount = CALCULATE(COUNTROWS('Emporia Device Usage'))

This Essentially returns the latest week where there are usages, I'm just trying to find a way to show this on the visual below it ? 

Thanks in Advance , James

[v-jtian-msft]

Hi,@JamesBurke,I am gald to help you.
According to your description I created the following test results.

You want to get a weekly usage, the measure you created worked, now your problem is trying to find a suitable visual to display it，but you don't know how to put the created measure into the visual, I recommend you to use the combined visual.

This way you can display the original data and at the same time show the measure you have created.

Please note that the results of the measuremay be affected by other filters or slicers.

I have the following questions about your code, if you can answer them for me or provide me with some non-sensitive data, it would be very helpful to solve your problem.

1. what is the meaning of the column [Year Week new], I replaced it in my own tests with the newly created calculated column weeknum (the number of weeks in the year of each date) in the table "Date".
2.what does the metric [Total Kwh] mean when filtered (I assume it is a metric because the first parameter in the calculate function is expression)

3.[Emporia Device Usage]

What exactly is the meaning of the column [Emporia Device Usage] that you ultimately want to calculate the function for, and does it refer to the usage of the device?

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[JamesBurke]

Hi @v-jtian-msft  , 

Thankyou for this , i'm looking for when nothing is selected the in the slicer the latest week of data will show by defualt , in the image above i have the daily average where instead but i would like the latest week to be displayed when no week is selected. 

when a selected week is applied by the slicer it will show that week instead. 

1)

Year Week new = 'Date'[Year] * 12 + 'Date'[Week Of Year] 

This is just a nermic value that i can refrence to get the Max usages -1 to compare agianst the week previous

2)  Total kWh 

Total Kwh = SUM('Emporia Device Usage'[Usages KWH])

This is just a total of the usages but the Measurement is kWh.

3) Emporia Device Usages

This refers to the devices usages of the emporia device ( which is a data logger) 

But yeah i wasn't clear , the image i attached was the daily average in the visual , i would like instead the latest week by defualt but can be filtered by a slicer. 

Thanks , James.

[v-jtian-msft · Accepted solution]

Hi,@JamesBurke .
Thank you for your detailed explanation and response.

According to your description, you want to set the default value of the slicer to the previous week's data, when viewing the dashboard, if you don't manipulate the slicer, then visual will display the previous week's browsing history by default (dynamic filtering data).

Solution1:

You need to create two calculate columns

1. Calculate the weekly number of weeks according to date.

weekNum = WEEKNUM('Date'[Dates],2)

2. If the data weekNum = WEEKNUM(today())-1, i.e., the week number of the data for the last week, it will be labeled as "LastWeek", and the rest of the data weekNum will be changed to “text” type.

LastWeek = 
 IF([weekNum]=  WEEKNUM(TODAY())-1,"LastWeek",FORMAT([weekNum],""))

Make the created calculate column "LastWeek" the default option for the slicer.

Republish the report to the Power BI Service.
like this:

Solution2
You can try using the ISFILTERED() function to determine if a field is currently being applied as a filter condition
Here is my test case.(I created a new page to show it)

IsSelected =
IF(ISFILTERED('Date'[LastWeek]),SUM('Date'[dailyUsageNum]),
CALCULATE(SUM('Date'[dailyUsageNum]),FILTER('Date','Date'[LastWeek]="LastWeek"))
)

I created a measure and put it into Visual's Y-axis to show: if the field [Last Week] is not being used as a filter at this time, it means that the slicer with [Last Week] as a filter field at this time is not selecting any value at this time, ISFILTERED('Date'[LastWeek])=false, and at this time, it is being used as a filter field with ' Date'[LastWeek]="LastWeek")) as a filter to display the results, that is, nothing to choose the case, the default display of the most recent week (the previous week) of data, otherwise that the slicer is selected, this time the normal use of the slicer value as a filter condition.

Here is the test result:

LastWeek =
IF([weekNum]= WEEKNUM(TODAY())-1,"LastWeek",FORMAT([weekNum],""))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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