cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

Want to show only 2nd half of month sales in current 6 months

Hi,

I have sales from jan 2021 to oct 2021, now I want to show 2nd half of month sales (i.e, for Sep month need to show sales from 16/09/2021 to 30/09/2021, for Oct month from 16/10/2021 to 31/10/2021) for current 6 months duration.

Thanks,

AshDil.

1 ACCEPTED SOLUTION
Super User

@AshDil , Create a measure like this with help from date table

LAst 6 month onlt last 15 days =
var _max = maxx(allselected('Date1'),'Date'[Date])
var _min = eomonth(_max,-6) +1
var _day = 16
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max && day('Date'[Date]) >=_day ) )

or

LAst 6 month onlt last 15 days =
var _max = maxx(allselected('Date1'),'Date'[Date])
var _min = eomonth(_max,-6) +1
var _day = 16
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max && day('Date'[Date]) >=_day ) )

or
LAst 6 month onlt last 15 days =
var _max = eomonth(today(),-1)
var _min = eomonth(_max,-6) +1
var _day = 16
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max && day('Date'[Date]) >=_day ) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

3 REPLIES 3
Community Champion

Try Create something like

``_Sales 2nd Half = CALCULATE(SUM('Table'[Amount]),FILTER(Date,DAY(Date[Date])>15))``

Proud to be a Super User!

Super User

@AshDil , Create a measure like this with help from date table

LAst 6 month onlt last 15 days =
var _max = maxx(allselected('Date1'),'Date'[Date])
var _min = eomonth(_max,-6) +1
var _day = 16
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max && day('Date'[Date]) >=_day ) )

or

LAst 6 month onlt last 15 days =
var _max = maxx(allselected('Date1'),'Date'[Date])
var _min = eomonth(_max,-6) +1
var _day = 16
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max && day('Date'[Date]) >=_day ) )

or
LAst 6 month onlt last 15 days =
var _max = eomonth(today(),-1)
var _min = eomonth(_max,-6) +1
var _day = 16
return
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max && day('Date'[Date]) >=_day ) )

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helper V

Hi @amitchandak ,

Thanks for your help. It worked for me.

Thanks,

AshDil.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.