Forum Discussion

agarwala's avatar
agarwala
Helper I
1 year ago
Solved

Deleted

Deleted

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi agarwala  If I understood correctly, to provide the flexibility of choosing dates along with the existing logic, you can use a combination of slicers and measures in Power BI.

    First create a date slicer with between slicer settings. Then create two measure for start and end date:

    SelectedStartDate = 
MIN('DateTable'[Date])

SelectedEndDate = 
MAX('DateTable'[Date])

    Here is the image:

    Modify your measure to use selected date if select else default calculation:

    Waste Quantity (t) =
VAR SelectedMonths = SELECTEDVALUE('Last Months Selection'[Number Of Months (Excludes Current Month)], 18) --- Default to 18

VAR CurrentDate = TODAY()
VAR StartDate = EDATE(CurrentDate, -SelectedMonths)
VAR EndDate = EOMONTH(CurrentDate, -1)

VAR DynamicStartDate = IF(ISBLANK([SelectedStartDate]), StartDate, [SelectedStartDate])
VAR DynamicEndDate = IF(ISBLANK([SelectedEndDate]), EndDate, [SelectedEndDate])

RETURN
CALCULATE(
    SUM(WASTE_API_DATA[Original Waste Quantity (t)]),
    WASTE_API_DATA[ACTIVITY_DATE] >= DynamicStartDate &&
    WASTE_API_DATA[ACTIVITY_DATE] <= DynamicEndDate
)

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and kudos!!

     

    Best Regards,
    Shahariar Hafiz

4 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi agarwala 

    It’s a bit unclear exactly what you’re trying to accomplish. Here’s a link on working with dynamic date periods in Power BI, which might help you set up the flexibility you need for selecting dates at runtime.

    https://www.youtube.com/watch?v=Xi86HHEaY_M

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

    If this doesn’t match your needs, could you please provide an example of what you’re trying to achieve? This will help us guide you more effectively.

  • johncolley's avatar
    johncolley
    Solution Sage
    1 year ago

    Hi agarwala ,


    Include in your date table the following variable:

      var _today = TODAY()

     

    As well as the following column:

     

    "Month Offset", DATEDIFF(_today, [Date column], MONTH)

     

    You can then use this Month Offset column in a filter or slicer to allow the user to select whatever range of months they require. Your current default would equate to -1 to -18, with 0 being the current month.

     

    Hope this helps.

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi agarwala  If I understood correctly, to provide the flexibility of choosing dates along with the existing logic, you can use a combination of slicers and measures in Power BI.

    First create a date slicer with between slicer settings. Then create two measure for start and end date:

    SelectedStartDate = 
MIN('DateTable'[Date])

SelectedEndDate = 
MAX('DateTable'[Date])

    Here is the image:

    Modify your measure to use selected date if select else default calculation:

    Waste Quantity (t) =
VAR SelectedMonths = SELECTEDVALUE('Last Months Selection'[Number Of Months (Excludes Current Month)], 18) --- Default to 18

VAR CurrentDate = TODAY()
VAR StartDate = EDATE(CurrentDate, -SelectedMonths)
VAR EndDate = EOMONTH(CurrentDate, -1)

VAR DynamicStartDate = IF(ISBLANK([SelectedStartDate]), StartDate, [SelectedStartDate])
VAR DynamicEndDate = IF(ISBLANK([SelectedEndDate]), EndDate, [SelectedEndDate])

RETURN
CALCULATE(
    SUM(WASTE_API_DATA[Original Waste Quantity (t)]),
    WASTE_API_DATA[ACTIVITY_DATE] >= DynamicStartDate &&
    WASTE_API_DATA[ACTIVITY_DATE] <= DynamicEndDate
)

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and kudos!!

     

    Best Regards,
    Shahariar Hafiz

  • agarwala's avatar
    agarwala
    Helper I
    1 year ago

    Thank you so much for all the suggestions.

    They really helped me.

    Arti