Create a calendar table to get the reporting month upto 15th as per selection in slicer

[chavanr]

Hi there,

I have a reporting requirement for some KPI to be reported upto the 15th of the month as per selection in slicer.

e.g. Kpi- Count of orders , data is starting from Jan 2021 to 15th oct latest.

so when user selects 'september' in the filter - he/she should be able to get the data starting Jan 2021 to 15th sept 2021.

Thanks for your help in advance.

Cheers !

[Anonymous]

@chavanr 

Please check my example, you need to replace the COUNT('Table'[Date]) with your count KPI.

Slicer Table = DISTINCT('Table'[Month])

Measure = CALCULATE(COUNT('Table'[Date]),FILTER('Table',[Date].[MonthNo]<SELECTEDVALUE(Slicer[Month])||[Date].[MonthNo]=SELECTEDVALUE(Slicer[Month]) && [Date].[Day]<=15))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@chavanr , Assume you have date table with month year.

measure =

var _max eomonth(maxx(allselected('Date'), 'Date'[Date]),-1) +15

return

Calculate(sum(Table[Values]), datesytd('Date'[Date]) , 'Date'[Date]<=_max)

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.

[chavanr]

Found a solution to the below- calculating the KPI as a measure & then use it as a sub-measure in your formula.

[chavanr]

Thanks for the solution @amitchandak 

Just a que- it is not giving me upto value when i use for the below measure

Var _max = EOMONTH(MAXX(ALLSELECTED('Calendar'),'Calendar'[Date]),-1)+15

return

CALCULATE(SUM(TCC_Kerbside[Occupancies]),FILTER((TCC_Kerbside),[Flag for Special HH] = 1),DATESYTD('Calendar'[Date]),'Calendar'[Date]<= _max)