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 Community!
Apologies if this has been raised previously or is very simple but I can't seem to find a clear soltuion to the following...
Given a source history table (think subscriptions to a service) for can I easily show a count over time as a trend?
Table Structure:
Customer ID (int) | Subscribed From (date) | Subscribed Until (date)
I want to display a graph which shows the number of active subscriptions over time.
The logic (in no particular language) would read as:
count(distinct
IF( [axis date] >= Subscribed From AND
( [axis date] < Subscribed Until OR Subscribed Until IS NULL)
THEN
Customer ID
ELSE
NULL
)
)
This is possible in Qlikview (bit of a hack) but I'm strugging with PowerBI
Any help appreciated, thanks.
Harry.
Solved! Go to Solution.
You may refer to the following DAX that creates a new table.
Table = ADDCOLUMNS ( CALENDARAUTO (), "Count", CALCULATE ( DISTINCTCOUNT ( Table1[Customer ID] ), FILTER ( Table1, Table1[Subscribed From] <= [Date] && ( Table1[Subscribed Until] > [Date] || ISBLANK ( Table1[Subscribed Until] ) ) ) ) )
Bump, anyone got any ideas here?
Thanks.
You may refer to the following DAX that creates a new table.
Table = ADDCOLUMNS ( CALENDARAUTO (), "Count", CALCULATE ( DISTINCTCOUNT ( Table1[Customer ID] ), FILTER ( Table1, Table1[Subscribed From] <= [Date] && ( Table1[Subscribed Until] > [Date] || ISBLANK ( Table1[Subscribed Until] ) ) ) ) )
Hi @v-chuncz-msft
I am using this DAX to be able to see how many tickets are open on a particular date.
Except in this formula it currently does not count the ticket that was opened and closed on the same date. Preferably also per department. Please see my topic here If you could help that'd be great.
Thanks @v-chuncz-msft I have used your recommended DAX, but I can't see how this will work for visualisations.
It seems like it just creates a table that counts each time increment for each year. How would you set up the visualisation so that you can identify open subscriptions over different time periods?
Cheers.
Thank you for this, @v-chuncz-msft! Worked beautifully.
What if I wanted to add another column that showed the number of subscribers that had been with us a year each day?
i tried this the figures don't much up really and the table begun from 01/01/1899 ??
Table =
ADDCOLUMNS (
CALENDARAUTO();
"Count"; CALCULATE (
COUNT ( Merge1[CallID] );
FILTER (
Merge1;
Merge1[NewColumn.DateAssign] <= [Date]
&& (
Merge1[NewColumn.DateResolv] > [Date]
|| ISBLANK ( Merge1[ClosedDate] )
)
)
)
)
Can't say for sure but suspect this is due to your data, please see the following.
I think autocalendar is doing this...
Thanks for this v-chuncz-msft, worked perfectly.
Just for everyone else's information, this solution is not fast (given it's having to count each row multiple times it's not surprising). For a large dataset you might want this info precalculated at source.
Very impressed with DAX so far!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |