Forum Discussion

Aman-K's avatar
Aman-K
Icon for Helper III rankHelper III
1 year ago
Solved

Adding measures in Power BI report

Hi guys,   I have created a report in Power BI which is extracting data from Google Analytics. I want to make few changes in my report so it looks like below :   And a slicer as below: ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Aman-K 

    I create a sample table:

     

    Then create a new table, here is the dax:

    TimePeriods = DATATABLE(
    "Period", STRING,
    {
        {"Today"},
        {"Yesterday"},
        {"Last 7 Days"},
        {"Last 14 Days"},
        {"Last 30 Days"}
    }
)

     

    Create a new measure again, try the following DAX expression:

    SelectedPeriod = 
VAR _date = MAX('Table'[Date])
RETURN
SWITCH(
    SELECTEDVALUE(TimePeriods[Period]),
    "Today", IF(_date = TODAY(), "Today", BLANK()),
    "Yesterday", IF(_date= TODAY() - 1,"Yesterday", BLANK()),
    "Last 7 Days", IF(_date >= TODAY() - 7 && _date<= TODAY(), "Last 7 Days", BLANK()),
    "Last 14 Days", IF(_date >= TODAY() - 14 && _date <= TODAY(), "Last 14 Days", BLANK()),
    "Last 30 Days", IF(_date >= TODAY() - 30 && _date <= TODAY(), "Last 30 Days", BLANK()),
    0
)

     

    Put this measure in Table view, and put TimePeriods's field in slicer, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
1 year ago

Hi Aman-K ,

 

I think, I would add a date dimension to start with. You can then add the date dimension to your model and create a relationship between your table and that dimension:

 

Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD

 

Then you can add a flag to the dimension that provides you with the options in your slicers. Alternatively, you could also use the filter pane in Power BI. Another way by utilising the date dimension is that you show your metrics per week so the fluctuations be less noticable. Lastly, you could also add a rolling average measure which smoothes the lines a bit more:

Rolling 12 Months Average in DAX - SQLBI

 

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Aman-K's avatar
Aman-K
Icon for Helper III rankHelper III
1 year ago

Thanks tackytechtom - I hae created a dimension as described in above link. How can I add a slicer for "Yesterday", "Today", "Last 7 days", "Last 14 days, "Last 30 days"? 

My dataset is as below:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Aman-K 

    I create a sample table:

     

    Then create a new table, here is the dax:

    TimePeriods = DATATABLE(
    "Period", STRING,
    {
        {"Today"},
        {"Yesterday"},
        {"Last 7 Days"},
        {"Last 14 Days"},
        {"Last 30 Days"}
    }
)

     

    Create a new measure again, try the following DAX expression:

    SelectedPeriod = 
VAR _date = MAX('Table'[Date])
RETURN
SWITCH(
    SELECTEDVALUE(TimePeriods[Period]),
    "Today", IF(_date = TODAY(), "Today", BLANK()),
    "Yesterday", IF(_date= TODAY() - 1,"Yesterday", BLANK()),
    "Last 7 Days", IF(_date >= TODAY() - 7 && _date<= TODAY(), "Last 7 Days", BLANK()),
    "Last 14 Days", IF(_date >= TODAY() - 14 && _date <= TODAY(), "Last 14 Days", BLANK()),
    "Last 30 Days", IF(_date >= TODAY() - 30 && _date <= TODAY(), "Last 30 Days", BLANK()),
    0
)

     

    Put this measure in Table view, and put TimePeriods's field in slicer, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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