Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Ignore blank dates

Best regards.

I seek to get the value of the last 3 months from my sales table, my table goes:
Table date: January 2022 to December 2023
Sales table with values: Sales from January 2022 to date
I use the following measure only that it takes the last months of the table (Sep 2023, Oct 2023, Nov 2023, Dec 2023) and gives me zero(0) because I have no values yet, I need to get the values of the dates with data.


last n months =
where maxfecha= CALCULATE(.MAX(DATE[DATE]), ALL( DATE))
where minfecha= DATE(YEAR(maxfecha), MONTH(maxfecha)-7, 1)
where result=
CALCULATE(
SALES[SALES$],
FILTER(
DATE,
DATE[DATE] >= minfecha && DATE[DATE] <= maxfecha
)
)
return
result


They can help me skip worthless dates.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Syndicate_Admin ,

    Because you do not provide sample data, so i could only give the solution according to your descriptions.

    Please try below dax formula:

    Measure =
VAR max_date =
    MAXX ( Sales, Sales[Date] )
VAR tmp =
    FILTER ( ALL ( Sales ), DATEDIFF ( Sales[Date], max_date, MONTH ) <= 3 )
RETURN
    SUMX ( tmp, Sales[SAles$] )

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies