Splitting Date field into 2 dates and find sales between dates (range)

[Anonymous]

Hi Team,

My requirement is to split a date field into Start Date(Min Date), End Date(Max Date) and use two dates in report as slicers to gte the sales for selected date range b/w Start date and end date.

see the sample data below for this,

using above data I need start date, End date where start date is min(date) and end date is max(date), two slicers i a report that should give sales in e respective date range.

for example if we choose Start Date: 1/1/2019 7 End Date: 10/01/2019 the sales should be 2080.

Quick help is appreciated.

Regards

Harsha

[Anonymous]

@Anonymous 

1) Create Date Table from existing date column in your data

DateTable = VALUES('Table'[Date])

2) Create Date Min measure

DateMin = IF(ISFILTERED(DateTable[Date]),FIRSTNONBLANK(DateTable[Date],1),BLANK())

3) Create Date Max measure

DateMax = IF(ISFILTERED(DateTable[Date]),LASTNONBLANK(DateTable[Date],1),BLANK())

4) Create Sales measure

TotalSales = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]>=[DateMin] && 'Table'[Date]<=[DateMax]))

5) Use Date from Date table in your slicer. Whenever you will select 2 dates or more from slicer, it will derive min and max from it and show the sales accordingly.

6) If you want 2 separate slicers then create 2 tables for Min and Max slicers, use them for Min and Max measures above. Keep slicers on Single select in selection control to avoid errors. Also make sure Min slicer value is always less than Max slicer value. or you can writte dax for it so that in either case it will show sales between date range.

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

[Tahreem24]

Hi @Anonymous ,

Power BI does it automatically for you. Please refer the below screen shot.

Please accept is as a solution If I answered your doubts!

[Anonymous]

As I know this, by default we can have date range filter but my req is to have 2 date fields as slicers Start Date , End Date derive from Date field and find the range between 2 dates.

[HotChilli]

Powerbi can do this automatically.

Just pull date on to the report surface and make it a slicer. It should default to a 'between' date slicer with the earliest and latest dates in the table)

Pull Sales on to a card (it should default to Sum in the field well)