Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
2 years ago
Solved

Weekly Data

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 ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    JamesBurke
    Helper III
    2 years ago

    Hi Anonymous  , 

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.