Set Result as Value for Today

[joshua1990]

Hello everybody!

I need some help to set a result of a measure as the value for today.

I have a fact sheet with the following structure:

Item	Date Created	Date Completed	Status	Value
1	01.01.2020	10.01.2020	100	500
2	01.01.2020		50	500

This table shows me all Orders in our system with the corresponding value, status etc...

Now I need to calculate the number of Orders that have a Status of 50. This result should be set as a value for today - just for today. There should be now values for other days.

How would you manage that?

Of course, I could add a calculated column with the date for today and filter on every order with Status 50, but how would you build that within a measure, without a calculated column?

Best Regards

[az38]

Hi @joshua1990 

what do you mean "for today" ?

what result do you expect based on your dummy data?

for example, you can try another calculated column 🙂

Column = CALCULATE(COUNTROWS('Table'), 'Table'[Status]=50)

[joshua1990]

Thanks for your reply!

The fact sheet is linked to my calendar table with the column „Date Created“.
If I use your provided measure, it shows me the sum for the corresponding day/ date.
But I need a total sum which is set for today. The result should be placed for today in the pivot table.

How would you do that?

[az38]

@joshua1990 

if I understand you correct try

Measure = 
CALCULATE(
COUNTROWS('Table'), 
FILTER(ALL('Table'), 
'Table'[Status]=50 && (ISBLANK('Table'[Date Completed]) || 'Table'[Date Completed] > TODAY()) && 'Table'[Date Created] < TODAY() 
)
)

[joshua1990]

Many thanks for your response. Unfortunately it is not what I am looking for. I would like the total to be assigned to today's date. The pivot table for today should contain the sum of all orders that have the status 50.

[az38]

@joshua1990 

how do you plan to define "orders assigned to today's date"?

do you need sum or count of orders?

[joshua1990]

Both, one for Count and one for Sum (Value)

[az38]

@joshua1990 what rule for  "orders assigned to today's date"? what is the orders? is it orders that have Date Created today or Date  Completed before today or what?

[joshua1990]

Just all orders with status 50, regardless an date

[az38]

@joshua1990 

these measures should work

CountOrders = CALCULATE(COUNTROWS('Table'), 'Table'[Status]=50)

SumOrders = CALCULATE(SUM('Table'[Value]), 'Table'[Status]=50)

if your status is not a whole number

CountOrders = CALCULATE(COUNTROWS('Table'), 'Table'[Status]="50")

SumOrders = CALCULATE(SUM('Table'[Value]), 'Table'[Status]="50")