DAX obtient la N'ième dernière valeur non vide

Question

Pour une date donnée, j'aimerais obtenir une moyenne des ventes des 3 derniers jours avec des ventes non vides. J'ai donc besoin de récupérer non seulement les dernières ventes en blanc (ce qui peut être facile) mais aussi les avant-dernières et troisièmes ventes en blanc. En général, j'ai besoin des N'èmes dernières ventes.

Exemple de données :

+------------+--------+--------+--------+--------+------------------+
|    Date    | Amount | N'th 1 | N'th 2 | N'th 3 | Expected Results |
+------------+--------+--------+--------+--------+------------------+
| 2021-02-01 |      1 |      1 |        |        |             1.00 |
| 2021-02-02 |      2 |      2 |      1 |        |             1.50 |
| 2021-02-03 |      2 |      2 |      2 |      1 |             1.67 |
| 2021-02-04 |        |      2 |      2 |      1 |             1.67 |
| 2021-02-05 |      3 |      3 |      2 |      2 |             2.33 |
| 2021-02-06 |        |      3 |      2 |      2 |             2.33 |
| 2021-02-07 |        |      3 |      2 |      2 |             2.33 |
| 2021-02-08 |      4 |      4 |      3 |      2 |             3.00 |
| 2021-02-09 |        |      4 |      3 |      2 |             3.00 |
| 2021-02-10 |        |      4 |      3 |      2 |             3.00 |
| 2021-02-11 |        |      4 |      3 |      2 |             3.00 |
+------------+--------+--------+--------+--------+------------------+

Le N'th 1 est la dernière vente "non-blank". Le N'th 2 est la "dernière avant un". Le résultat attendu est la moyenne de N1, N2, N3.

Lien vers un exemple de fichier de données avec les solutions suggérées par la réponse acceptée :
Moyenne roulante DAX NonBlanks.pbix

Answer 1

Voici mon point de vue (c'est une mesure) :

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = FILTER(ALL(Data), NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate)
var rankedTab = FILTER ( ADDCOLUMNS ( nonBlankTab, "Rank", RANKX ( nonBlankTab, [Date] ) ), [Rank] <= 3 )
return AVERAGEX(rankedTab, [Amount])

EDITAR :

Juste une explication :

• la mesure est calculée pour la date sélectionnée. Si aucun contexte de date n'est présent, la date la plus récente est prise en compte.
• Je filtre ensuite le tableau pour qu'il ne contienne que les lignes dont les ventes ne sont pas vides et ne sont pas postérieures à la date courante.
• Ensuite, je classe les dates de manière à ce que les trois dernières dates soient toujours classées 1, 2 et 3.
• Je filtre ensuite toutes les dates dont le rang est supérieur à 3.
• Enfin, je calcule une moyenne sur les 3 points de données restants.

EDIT2 :

J'ai un peu simplifié la mesure - lastSalesDate n'est pas nécessaire. De plus, comme demandé dans les commentaires, j'ai laissé la première tentative telle quelle, et voici la version modifiée avec TOPN au lieu de la combinaison ADDCOLUMNS/RANKX/FILTER :

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = FILTER(ALL(Data), NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate)
var rankedTab = TOPN(3, nonBlankTab, [Date])
return AVERAGEX(rankedTab, [Amount])

EDIT3 :

Une version plus universelle de la mesure, qui supprime simplement les filtres de la Date ce qui est en fait tout ce dont nous avons besoin. Il n'est pas nécessaire de massacrer tous les autres filtres de la table :

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = CALCULATETABLE(FILTER(Data, NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate), REMOVEFILTERS(Data[Date]))
var rankedTab = TOPN(3, nonBlankTab, [Date])
return AVERAGEX(rankedTab, [Amount])

Answer 2

Tout d'abord, créez les 3 mesures ci-dessous.

n1 = 
VAR current_date = MIN(your_table_name[Date])
VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = first_max_date_with_no_blank
    )
)

n2 = 
VAR current_date = MIN(your_table_name[Date])

VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

VAR second_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < first_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = second_max_date_with_no_blank
    )
)

n3 = 
VAR current_date = MIN(your_table_name[Date])

VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

VAR second_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < first_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

VAR third_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < second_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = third_max_date_with_no_blank
    )
)

Créez maintenant cette dernière mesure

average = 

VAR sum_sales = [n1] + [n2] + [n3]
VAR devide_by = IF([n1] = BLANK(),0,1) + IF([n2] = BLANK(),0,1) + IF([n3] = BLANK(),0,1)

RETURN DIVIDE(sum_sales,devide_by)

Voici le résultat final -