Forum Discussion

Gumberculeez's avatar
Gumberculeez
Icon for Helper II rankHelper II
6 years ago
Solved

Multi query sum showing only today's date

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! 🙂
 

 

 

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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.

25 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    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's avatar
      Gumberculeez
      Icon for Helper II rankHelper II
      6 years ago

      I'm using an SQL direct query.  

       

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

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi Gumberculeez ,

        create a date table

        Date = 
ADDCOLUMNS (
        CALENDARAUTO( ); 
    "Year"; YEAR ( [Date] );
    "Month Name"; FORMAT ( [Date]; "mmmm" );
    "Month Number"; MONTH ( [Date] );
    "Weekday"; FORMAT ( [Date]; "dddd" );
    "Weekday number"; WEEKDAY( [Date] );
    "Quarter"; "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1;
    "Day of Month"; Day([Date])
)

        add the relationship to the date table

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

         

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

        Please give Kudos for support.

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Gumberculeez
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago
        Hi,
        maybe you could share a screenshot of your model, with the relations.