Dynamic Category based on selected date

[VinnieHuub]

Hi all,

I'm trying to create a new category column to an existing table, but it needs to be calculated based on a date selected by the end user.

F.e. we have this simple table called Customer:

Custnr	Type	Logindate	Balance
1	Joint	1/1/2020	100
2	Joint	1/5/2020	10000
3	Single	1/6/2020	400
4	Minor	1/9/2020	0
5	Minor	1/12/2020	50000
6	Single	1/2/2021	10
7	Self Employed	1/3/2021	30
8	SME	1/8/2021	40

A new column needs to be added based on the logindate and a selected date. If the logindate <= selecteddate, then Yes else No.
(A calculated column is a possibility, but then the dataset needs to be refreshed every time)

I tried the following:

create a date table, not related to any other table, to be used as a slicer

create a new table based on the existing adding the logincategory:

LoginCat =

VAR OnDate = date(2020,1,16)

RETURN

CALCULATETABLE(

ADDCOLUMNS(CUSTOMER, "LoginCat", if(CUSTOMER[Logindate] <= OnDate, "Yes", "No"))

)

 This works fine with the date hard-coded:

But when I change the variable to = selectedvalue(calendar[date]) it doesn't pick up the selected date.

Any idea?

Thank you.

Vincent

[v-zhangti · Accepted solution]

Hi, @VinnieHuub 

You can try the following methods.

Date = CALENDAR(DATE(2020,1,1),DATE(2021,8,31))

Measure:

Measure = 
IF (
    MAX ( 'Date'[Date] ) >= SELECTEDVALUE ( 'Customer'[Logindate] ),
    "Yes",
    "No"
)

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@VinnieHuub ,

First Create a measure

M1= if(Max(Table[logindate]) <= selectedvalue('Date'[Date]), "Yes", "No")

Create a table/Enter data, with two rows  Yes, No

or a new Table

Bucket = Union(row("Flag", "Yes") , rows("Flag", "No" ))

Create a measure now

sumx(filter(values(Table[Custnr]) , [M1] = max(Bucket[Flag]) ), Calculate(Sum(Table[Balance] )) )

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k