cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Making a filtered measure into a constant

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Making a filtered measure into a constant

Hi @rynelees,

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

 

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Making a filtered measure into a constant

Hi @rynelees,

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

Highlighted
Helper I
Helper I

Re: Making a filtered measure into a constant

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.

Highlighted
Microsoft
Microsoft

Re: Making a filtered measure into a constant

Hi @rynelees,

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

Highlighted
Helper I
Helper I

Re: Making a filtered measure into a constant

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. 

Highlighted
Microsoft
Microsoft

Re: Making a filtered measure into a constant

Hi @rynelees,

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

 

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors