Multi query sum showing only today's date

[Gumberculeez]

Hey All;

Curious how I can do this?  I have the multi query sum down...

Mileage plus Mileage =

SUM('SCInvLabor'[Mileage]) + SUM('SCOpenLabor'[Mileage])  

however, I'd like to add a filter to show just the above just for today.

an assist would be fantastic! 🙂

[v-lili6-msft]

hi @Gumberculeez 

You could just adjust the formula as below:

Mileage plus Mileage =
CALCULATE ( SUM ( 'SCInvLabor'[Mileage] ), 'SCInvLabor'[Date] = TODAY () )
    + CALCULATE ( SUM ( 'SCOpenLabor'[Mileage] ), 'SCOpenLabor'[Date] = TODAY () )

and you could also use mwegener's way  that add a date table and then create the relatinship with fact table and just filter as 'Date'[Date] = TODAY()

Regards,

Lin

[Gumberculeez]

interesting.... I'm not getting any errors now with both solutions... however the formula itself is not working it seems...

It's "refresfing" however, it just maintains (BLANK) even though displaying each of those seperately provide numbers.  

Could it have something to do with this being direct query information?

[mwegener]

Hi,
maybe you could share a screenshot of your model, with the relations.

[Gumberculeez]

for sure... and thanks for the continued assist.  this is new territory for me 🙂

[mwegener]

Looks good.

Are new data in the table?

Have filters been set?

[Gumberculeez]

there is ya...  and filters are on.  

This pic shows the 2 queries with the measure we are working toward on top...

[Gumberculeez]

Could this be the issue guys?  Seems the dispatchtime is pulling over a time stamp as well.... is it messing with the Today value?

[mwegener]

SQL:

SELECT
CAST(DispatchDate as Date) AS DispatchDate
....

[mwegener]

Yes, i think so.

[mwegener]

How, you create the queries?
Power Query or SQL?

[Gumberculeez]

I'm doing a direct query from the SQL database in PowerBI.

[mwegener]

Hi @Gumberculeez ,

with SQL you can use a CASE, to convert a datetime to date

CAST(DispatchDate as Date) AS DispatchDate

with Power Query you can change the datatype from datetime to date

If I answer you question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Gumberculeez]

Just to confirm, I'm required to do this in SQL Studio or PowerBI?

I'm direct query'ing data directly from our business system's SQL database.  So I not altering that.... lol

[mwegener]

Hi @Gumberculeez 

at Power BI.

Could you share a screenshot from your query?

If I answer you question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Gumberculeez]

this is the current statement I use...

[mwegener · Accepted solution]

Hi @Gumberculeez ,

than try this.

SELECT
*,
CAST(DispatchDate as Date) AS DispatchDate_New
FROM _custom_eviews.RTPowerBI (NOLOCK)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Gumberculeez]

It's alive! 😄 

fully working now.  thanks so much for the assist.

[mwegener]

Hey @Gumberculeez ,

you are welcome!

[mwegener]

Hi @Gumberculeez ,

maybe you could post more information about your model.

Do you use a date table?

If I answer you question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Gumberculeez]

I'm using an SQL direct query.  

Each query I'm bringing in has a date field.  However, either will impact the measure.