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.
I'm trying to calculate the number of operating days completed in the current month and then use this number (currently 😎 across past months.
My formula looks like:
Completed Operating Days = CALCULATE(sum('Date'[Operating Day])) <---- this is currently 8
I want to take revenue divided by total month operating days multiplied by completed operating days to equal revenue at this current time in previous months. So where we were at after 8 operating days in past months.
However, everytime I try to use the completed operating days it does it for past months. I just want the number 8. Even when I use a filter to make the measure only the sum of the current month, it doesn't work in past months.
I would love to make the completed operating days measure and then make another one to make 8 a constant. Is this possible? Is there a better way to make my formulas accessible accross different months?
Solved! Go to Solution.
Hi @Anonymous,
After add the calculated column [Current MTD Completed Operating Days], you can create a calculate a column using the formula.
result=CALCULATE(SUM(Table[Current MTD Completed Operating Days]),FILTER(Table, Table[Month]<MAX(Table[Month])&&Table[Year]=MAX(Table[Year])))
Best Regards,
Angelia
Hi @Anonymous,
You can fix it using a filter, for example, you calculated the Jan month operating days.
Completed Operating Days = CALCULATE(sum('Date'[Operating Day]),Filter('Date','Date'[Month]=1))
When you add it to other measure, it is same.
In addition, if this still does not solve your issue, please share more details and some table for further analysis.
Best Regards,
Angelia
Thanks for the reply but it doesn't work... I already tried a different variation of your solution and it only makes the [completed operating days] applicable to June 2017. I'm assuming this is because I have it filtered to June so all of the previous months can't access it.
Is there a way I could make it a variable? Would that allow me to use it despite the filter? I could provide more information if you need it. Let me know. Thanks.
Hi @Anonymous,
It's still confusing your requirement. Do you mind create some sample data and list the expected result?
We can create a variable in DAX formula, I am not sure if it's what you want. You can use the following similar pattern.
VAR Completed Operating Days = CALCULATE(sum('Date'[Operating Day])) RETURN CALCULATE()
Best Regards,
Angelia
I'm not entirely sure how to provide sample data but I'll try and break it down more...
I have our business' transaction history through odata. I made a date table off of the distinct date values from transaction dates.
I made a calculated column to determine operating days (those days where weekday is not Sunday and some random holidays).
I try to create:
Current MTD Completed Operating Days = CALCULATE( SUM('Date'[Operating Day]), filter('Date'[Month No]=[Current Month]))
And then bring up a table where the values are [Month Year] and [Current MTD Completed Operating Days].
Before I add [Current MTD Completed Operating Days] the table looks like:
Month Year
December 2016
January 2017
February 2017
March 2017
April 2017
May 2017
June 2017
After I add the measure:
Month Year Current Completed.....
June 2017 8
I want this measure to be able to be used against the other months as well. End goal is to determine where sales were at in previous months after the number of current operating days.
Let me know if you need any more info.
Hi @Anonymous,
After add the calculated column [Current MTD Completed Operating Days], you can create a calculate a column using the formula.
result=CALCULATE(SUM(Table[Current MTD Completed Operating Days]),FILTER(Table, Table[Month]<MAX(Table[Month])&&Table[Year]=MAX(Table[Year])))
Best Regards,
Angelia
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 | |
101 | |
86 | |
64 |