I’m stuck on this challenge where I neet to countdistinct Customer ID per marketing status using a Date Slicer.
Having a fact table which has the following columns:
- “leadID” = customer ID
- “funnel_possy” = marketing status of the Customer.
- “date” = date where the customer change the status.
I need to calculate two columns:
- BalanceStart: distinctcount of Customer ID per “funnel_possy” of the minimum date of the Date Slicer.
- BalanceEnd: distinctcount of Customer ID per “funnnel_possy” of the maximum date of the Date Slicer.
This table below is one Customer ID and all the status changed through the dates:
In the example below is how I want it to calculate the table. Accordingly with the slider, the column “nBalanceStart” it should distinctcount how many Customer ID it contains in each “funnel_possy” status in the ealiest date of the Slicer. And the same with the “nBalanceEnd” column but distinctcount the latest date of the slicer.
Mkt Report - Forum.pbix (153.9 KB)