Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone 🙂
I will be happy if someone could solve my problem.
I have tabel with historical data:
Date ID_clients Service_active Service_purchased Active_days (this I want to calculate!)
1.1.2022 A 0 0 0
2.1.2022 A 1 1 1
3.1.2022 A 1 0 2
4.1.2022 A 1 0 3
5.1.2022 A 1 1 1
6.1.2022 A 1 0 2
7.1.2022 A 1 0 3
1.1.2022 B 0 0 0
2.1.2022 B 0 0 0
3.1.2022 B 1 1 1
4.1.2022 B 1 0 2
5.1.2022 B 1 0 3
6.1.2022 B 1 0 4
7.1.2022 B 0 0 0
Service_active is showing are the service was active in specific date. 1 if is, 0 if is not.
Service_purchased is showing are the service was active in specific date. 1 if is, 0 if is not.
Every client have one row for each day in table.
I want to get calculated columne with data in each row who will representing how many days was services active starting from day when was purchased by clients. The column Active_days representing what I need to. Each time when clients purchase services we start counting active days from 1.
If you are missing some information, be free to write me.
Thanks in advanced!
Vedran
Solved! Go to Solution.
Hi @VedranR
first create this calculated column
Qty_Purchased =
VAR CurrentDate = Data[Date]
RETURN
SUMX (
CALCULATETABLE (
Data,
ALLEXCEPT ( Data, Data[ID_clients] ),
Data[Date] <= CurrentDate
),
Data[Service_purchased]
)
Then create a second calculated column
Active_days =
VAR CurrentDate = Data[Date]
RETURN
SUMX (
CALCULATETABLE (
Data,
ALLEXCEPT ( Data, Data[ID_clients], Data[Qty_Purchased] ),
Data[Date] <= CurrentDate
),
Data[Service_active]
)
Hi @VedranR
first create this calculated column
Qty_Purchased =
VAR CurrentDate = Data[Date]
RETURN
SUMX (
CALCULATETABLE (
Data,
ALLEXCEPT ( Data, Data[ID_clients] ),
Data[Date] <= CurrentDate
),
Data[Service_purchased]
)
Then create a second calculated column
Active_days =
VAR CurrentDate = Data[Date]
RETURN
SUMX (
CALCULATETABLE (
Data,
ALLEXCEPT ( Data, Data[ID_clients], Data[Qty_Purchased] ),
Data[Date] <= CurrentDate
),
Data[Service_active]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
73 | |
50 | |
45 | |
20 | |
17 |