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.
Solved! Go to Solution.
Hi @GunnerJ ,
You could try below measure to see whether it work or not
Measure 2 = CALCULATE(COUNTROWS(DISTINCT('Master by Substation'[BI_ACCT])),FILTER(ALLSELECTED('Master by Substation'),or('Master by Substation'[BI_SRV_STAT_CD] = 1, 'Master by Substation'[BI_SRV_STAT_CD] = 18)&&'Master by Substation'[BI_CON_DT]<=MIN('Master by Substation'[BI_CON_DT]) && 'Master by Substation'[BI_CON_DT]>=DATE(YEAR(TODAY())-1,month(TODAY())+1,1)))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @GunnerJ ,
I am not clear about your requirement, I need to check the requirement with you. It seems that you want to calculate running total, you said that " If account#123 was active and had a connect date in FEB 2020 I would want it to contribute to the total in Feb and March of 2020 but not for Jan 2020 or any date before that. ", did you want to use slicer to control date range? And did you just want to show selected month and later months instead of last 12 month of current selected month?
If this is not what you want, please correct me and explain more detailed logic to me.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the reply.
I'm hoping for the measure to be dynamic in that it will adapt to however many months I have displayed it will show the running total for the given months. I have a date table setup and at first I would likely limit it to the last 12 months. If using a slicer would help I am fine with that.
So for example as of today my chart would show April 2019 - March 2020 (current month). That way we can see growth over a year's time.
In a small scale example if we had accounts with the following connect dates: Acct#1 Connect Date: Dec 12th 2019, Acct#2 Connect Date: Jan 8th 2020, Acct#3 Connect Date: Jan 21st 2020, Acct#4 Connect Date: Feb 2nd 2020, and Acct#5 connect Date: March 10th 2020.
The running totals would look like this: Dec 2019: 1 account, Jan 2020: 3 accounts, Feb 2020: 4 accounts, March 2020: 5 accounts.
The graph would easily visualize that growth over time.
I would like to build this logic on top of a previous measure that had the service statuses and revenue class criteria.
Master Sum Res = CALCULATE(COUNTROWS(DISTINCT('Master by Substation'[BI_ACCT])), 'Master by Substation'[BI_REV_CLASS_CD] = 1, or('Master by Substation'[BI_SRV_STAT_CD] = 1, 'Master by Substation'[BI_SRV_STAT_CD] = 18))
Does this help explain things?
Hi @GunnerJ ,
You could try below measure to see whether it work or not
Measure 2 = CALCULATE(COUNTROWS(DISTINCT('Master by Substation'[BI_ACCT])),FILTER(ALLSELECTED('Master by Substation'),or('Master by Substation'[BI_SRV_STAT_CD] = 1, 'Master by Substation'[BI_SRV_STAT_CD] = 18)&&'Master by Substation'[BI_CON_DT]<=MIN('Master by Substation'[BI_CON_DT]) && 'Master by Substation'[BI_CON_DT]>=DATE(YEAR(TODAY())-1,month(TODAY())+1,1)))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@dax I tried the code but was not successful. We're going to try a different approach as it's a pretty unique challenge. Thank you for taking the time to look into the issue.
@dax was the above detail enough to help proceed with finding a solution? I'm curious as this is a rather confusing issue for me. Thanks
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |