Forum Discussion

Peter_au's avatar
Peter_au
Icon for Helper I rankHelper I
6 years ago
Solved

Date filter, unexpected results

Hello I am using a simple date filter expression to sum values below a date picked by a user.

 

The dates are supplied by a date table which is related to the dates in the value table. The values sum fine, until a user picks a date with no date in the value table and the summed value ends up as the first value in the data table.

 

total_tonnes_transfer = calculate(
qry_Sous_Tirage_Transfer[transfer_tonnes],
filter(all(qry_Sous_tirage),
qry_Sous_tirage[Date_Transfert] <= Date([selected_Year],[Selected_month_2],[Selected_Day_nb2])
&& qry_Sous_tirage[Date_Transfert]<= max(qry_Sous_Tirage_Transfer[Date_Transfert])
)
)

e.g.
Tonnes        Selected date
21521            2020-06-02


Tonnes      Selected Date
336              2020-06-03

 

Any ideas?
Thanks

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Peter_au ,

     

    Just add a filter condition like this and check the "show items with no data" option:

    total_tonnes_transfer = calculate(
SUM('Table'[Tonnes_Blast_id]),
filter(all('Table'),
'Table'[Date_Transfert] <= SELECTEDVALUE('Table 2'[Date])
&&SELECTEDVALUE('Table 2'[Date]) in VALUES('Table'[Date_Transfert])
)
)

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    I'm a bit confused by your formula - is that a MEASURE?

    Where is the SUM part of it? Do you have a DimDate table?

    total_tonnes_transfer = SUMX(filter(all(qry_Sous_tirage),
    qry_Sous_tirage[Date_Transfert] <= Date([selected_Year],[Selected_month_2],[Selected_Day_nb2]),
    qry_Sous_Tirage_Transfer[transfer_tonnes])

    But without seeing your calculations for Selected Year, Selected Month, etc I can't be sure if that will work. I think using a DimDate table would be better, and having user filter for SELECTEDVALUE(DimDate[Date])
      • Peter_au's avatar
        Peter_au
        Icon for Helper I rankHelper I
        6 years ago

        Data:

         

        Date_TransfertTonnes_Blast_id
        2020-06-18480
        2020-06-191248
        2020-06-19336
        2020-06-19288
        2020-06-19240
        2020-06-19336
        2020-06-19480
        2020-06-20144
        2020-06-20288
        2020-06-20480
        2020-06-21384
        2020-06-2148
        2020-06-21960
        2020-06-21720
        2020-06-2248
        2020-06-22528
        2020-06-22432
        2020-06-23240
        2020-06-23384
        2020-06-23384
        2020-06-2396
        2020-06-2448
        2020-06-241392
        2020-06-2496
        2020-06-24384
        2020-06-24480
        2020-06-25288
        2020-06-25480
        2020-06-25912
        2020-06-2548
        2020-06-25288
        2020-06-25144
        2020-06-26336
        2020-06-26816
        2020-06-2696
        2020-06-28288
        2020-06-28288
        2020-06-282160
        2020-06-2848
        2020-06-281200
        2020-06-29480
        2020-06-29288
        2020-06-30240
        2020-06-30240
        2020-06-30336
        2020-07-01480
        2020-07-01336
        2020-07-01288
        2020-07-02288
        2020-07-02288
        2020-07-0248
        2020-07-02480
        2020-07-03384
        2020-07-03288
        2020-07-03144

         

        Measure:

         

        total_tonnes_transfer = calculate(
        qry_Sous_Tirage_Transfer[transfer_tonnes],
        filter(all(qry_Sous_Tirage_Transfer),
        qry_Sous_Tirage_Transfer[Date_Transfert] <= Date([selected_Year],[Selected_month_2],[Selected_Day_nb2])
        && qry_Sous_Tirage_Transfer[Date_Transfert]<= max(qry_Sous_Tirage_Transfer[Date_Transfert])
        )
        )
         
        Dates:

         

        Selected_date = format(DATEVALUE([selected_Year] & "-" & [Selected_Month_Abv] & "-" & [Selected_Day_nb2]),"YYYY-MM-DD")
        Selected_Day_nb2 = SELECTEDVALUE(date_Tbl[Day_nmb_2])
        Selected_month_2 = month([Selected_date])
        Selected_Month_Abv = selectedvalue(date_Tbl[Month_Abv])
        selected_Year = SELECTEDVALUE((date_Tbl[Year]))
         
        Expected result:
         
        total_tonnes_transferSelected_date
        109442020-06-24

         

         or

        total_tonnes_transferSelected_date
        183362020-06-28

         

         

         Unexpected result

        total_tonnes_transferSelected_date
         2020-06-27
         
        Here is the information that was requested. 
        If the date is not it the data, there is no sum.
         
        Thanks