Forum Discussion
Date Filter range
gco , Create a calendar table and do NOT join it with any date . Code at the end
Then create a measure like
new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Table', ('Table'[date1] >=_min && 'Table'[date1] <=_max ) || ('Table'[date2] >=_min && 'Table'[date2] <=_max) ))
Calendar code, Date Table (Mark this as date table). Use in slicer
Date= Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
And say for example, i have the data.
Branch,account,date1,date2,datefilter
123,12345,2022-01-01,2022-10-11,1
123,12345,2022-01-01,2022-10-11,1
345,34565,2022-01-01,NULL,1
434,2234,NULL,2022-10-11,1
434,4456,NULL,2022-01-01,1
How can i do the distinct count by branch? So like if the datefilter = 1 (meaning they are within the date slider), i want my table to show.
Branch,date1count,date2count
123,1,1
345,1,0
434,0,2