Deleted

[agarwala]

Deleted

[agarwala]

Thank you so much for all the suggestions.

They really helped me.

Arti

[shafiz_p · Accepted solution]

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

[johncolley]

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.

[Ritaf1983]

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.