Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
giorgiokatr
Helper V
Helper V

Past 6 months for every month of selection in filter

i have a slicer with month 01/2022-02/2022 etc

I click on 01/2022 and 02/2022 and i use this formula to sum my customers for 6months back for each month 

 

customers6m =
CALCULATE(DISTINCTCOUNT(DoB[CUSTOMERID]),DATESBETWEEN(
'Table'[Date],
DATEADD(STARTOFMONTH('Table'[Date]) , -5 , MONTH ),
DATEADD(ENDOFMONTH('Table'[Date]) , 0, MONTH )
))

 

I would like to make the slicer single selection and when i select 02/2022 to have the same calculation (01/2022 and 02/2022 - ytd ) (dax sould calculate the 6 months back for each month)

2 REPLIES 2
amitchandak
Super User
Super User

@giorgiokatr , if you want to show more dates then selected date in slicer, Then slicer needs to me on an independent date table 

 

 

//Date1 is independent Date table and Date is Joined
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Table[date_closed]),-6,MONTH)) , filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

@amitchandak thanks! i noticed that var _min = minx(allselected(Date1),Date1[Date]) shows 01/02/2022 (when 02/2022) is selected. I want to have 01/01/2022

also it does work but sum customers only for 02/2022 (and 6 months back) 01/2022 is ignored

i want to have (slicer single selection 02/2022)

02/2022: customers of 02/2022 and 6 months back

01/2022: customers of 01/2022 and 6 months back

ytd period!

it works perfect when slicer is multiselect and i check 01-02/2022

when single select it does not work

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.