Forum Discussion

azaterol's avatar
azaterol
Helper V
4 years ago
Solved

Date before today

Hey all,

I want all sales untiltoday. 

As you can see, we have also sales for tomorrow, for 06.07, for 07.07 and for 08.07. These dates are in the future.
But I only want the Date before Today, but everytime it gives me the last value in the date table, which is 08.07.2022. 

 

You have an idea, how I can modify my DAX? 

 

Here is my DAX : 

Sales until today = CALCULATE(LASTDATE('Date'[Date]),FILTER(AUDOK, AUDOK[DokDate] < DATE(2022,07,TODAY())))


 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you all. 

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi azaterol 
    Please try 

    Sales until today =
VAR LastDate =
    CALCULATE (
        MAX ( AUDOK[DokDate] ),
        AUDOK[DokDate] < TODAY (),
        REMOVEFILTERS ( 'Date' )
    )
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR EndDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( LastDate ), DAY ( LastDate ) )
VAR StartDate =
    STARTOFYEAR ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( AUDOK[Sales] ),
        'Date'[Date] >= StartDate,
        'Date'[Date] <= EndDate,
        REMOVEFILTERS ( 'Date' )
    )

10 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    4 years ago

    Hey azaterol ,

     

    you have to modify your formula like so:

     

    Sales before today =
SUMX(
FILTER(AUDOK, AUDOK[DokDate] < TODAY())
, AUDOK[SALES]
)

     

     

    or something similar to this, depending on your data model:

    M2 =
CALCULATE(
SUM('table'[sales])
,  'date'[date] < today() 
)

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

    • azaterol's avatar
      azaterol
      Helper V
      4 years ago

      Hey TomM , thank you!

      How can I modifiy my code for 2020(01.01. until todays date 04.07.2020) and 2021(01.01. until todays date 04.07.2021). 

      So I want to show the same dates for the last years. 

      I tried to modify your code, but It wont work, it shows me the whole sales from 2020 until today.

       

      Sales before today 2020=
      SUMX(
      FILTER(AUDOK, AUDOK[DokDate] >=date(2020,01,01) && AUDOK[DokDate]< date(2020,07, TODAY())
      , AUDOK[Sales]
      )
      Thank you for your help!
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi azaterol 
    Please try

    Sales until today =
CALCULATE (
    MAX ( AUDOK[DokDate] ),
    AUDOK[DokDate] < TODAY (),
    REMOVEFILTERS ( 'Date' )
)
  • AliceW's avatar
    AliceW
    Power Participant
    4 years ago

    I would have a couple of solutions.

    1. Just add a table (or page, or report filter) on Date. Use 'Relative date', and show items when the value is in the last, say, 10 years. It will only display dates in the past. Of course if you have more than 10 years worth of data, you should expand the period.

     

    2. In the Date table, build a column. It will be helpful for future filters and calculations.

    Date Status = if('Dates'[Date].[Date]<TODAY(),"In the Past",
    if('Dates'[Date].[Date]=TODAY(),"Today",
    "In the Future"
    ))
     
     

     

     

     

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    azaterol 

    Please try

    Sales until today =
VAR EndDate =
    CALCULATE (
        MAX ( AUDOK[DokDate] ),
        AUDOK[DokDate] < TODAY (),
        REMOVEFILTERS ( 'Date' )
    )
VAR StartDate =
    STARTOFYEAR ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( AUDOK[Sales] ),
        'Date'[Date] >= StartDate,
        'Date'[Date] <= EndDate,
        REMOVEFILTERS ( 'Date' )
    )
    • azaterol's avatar
      azaterol
      Helper V
      4 years ago

      Hey tamerj1 thank you for answer, but it wont work for this  Problem.

       

      How can I modifiy my code for 2020(01.01. until todays date 04.07.2020) and 2021(01.01. until todays date 04.07.2021). 

      So I want to show the same dates for the last years. 

      I tried to modify your code, but It wont work, it shows me the whole sales from 2020 until today.

       

      Sales before today 2020=
      SUMX(
      FILTER(AUDOK, AUDOK[DokDate] >=date(2020,01,01) && AUDOK[DokDate]< date(2020,07, TODAY())
      , AUDOK[Sales]
      )
      Thank you for your help!
      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi azaterol 
        Please try 

        Sales until today =
VAR LastDate =
    CALCULATE (
        MAX ( AUDOK[DokDate] ),
        AUDOK[DokDate] < TODAY (),
        REMOVEFILTERS ( 'Date' )
    )
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR EndDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( LastDate ), DAY ( LastDate ) )
VAR StartDate =
    STARTOFYEAR ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( AUDOK[Sales] ),
        'Date'[Date] >= StartDate,
        'Date'[Date] <= EndDate,
        REMOVEFILTERS ( 'Date' )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi azaterol,

    Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng