Making a filtered measure into a constant

[Anonymous]

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?

[v-huizhn-msft]

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

[Anonymous]

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.

[v-huizhn-msft]

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

[Anonymous]

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. 

[v-huizhn-msft · Accepted 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