Forum Discussion

danielpcamara's avatar
danielpcamara
Resolver I
5 years ago
Solved

Help with DAX - Count Using two coluns Date as Filter

So, I have a data set that is compose of a List of Tasks to do that can be recurrent.

I want to evry task that is Conclud, and that is program to end this month so I try:

 

 

 

Concluded Tasks = CALCULATE(
    COUNT(Tasks[Task]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Mes] = MAX('Calendar'[Mes]) && 
        'Calendar'[Ano] = MAX('Calendar'[Ano])
    ),
    FILTER(
        Tasks,
        Tasks[Conclusion Date] <=  MAX('Calendar'[Data_id])
    )
)

 

 

 

but I get this:

 

when I was expecting something like this (not exactly this but close):

 

 

All Dates ar in yyyyMMdd in the table Tasks.

More Information:

My Tables are:

Calendar[Data_id] 1 -> * Tasks[Expected Date] (active)

Calendar[Data_id] 1 -> * Tasks[Conclusion Date] (inactive)

Calendar[Data_id] 1 -> * Tasks[Reference Date] (inactive)

Calendar[Data_id] 1 -> * Tasks[Reference Date] (active, just for filter)

Source code table Task:

 

 

 

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZA9C4MwEIb/imR2uA+T0rWFglt3cchgp9SC2v9fFfVCSAIWArnhnvd5uaZRd9fZvkBVqmn4dvNHQDA/fYxY7SMCqrZMMSwMC0NZpgJzjMiSlGO0eDReJCnHmJWRBH+X/ugUMn4nSHQKGeMxdI1344yHMO4JGc9jSIeex2d422k92Mu6Ua6AOjZuVN2Pk3XF81aH3JK7LzMkbHTOtlF8gmp/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Recurrent = _t, #"Expected Date" = _t, #"Conclusion Date" = _t, #"Reference Date" = _t]),
    #"Valor Substituído" = Table.ReplaceValue(Fonte,"",null,Replacer.ReplaceValue,{"Expected Date", "Conclusion Date", "Reference Date"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Valor Substituído",{{"Task", type text}, {"Recurrent", type logical}, {"Expected Date", type text}, {"Conclusion Date", type text}, {"Reference Date", type text}})
in
    #"Tipo Alterado"

 

 

 

Source code table Calendar:

 

 

let
    Fonte = List.Dates(#date(2020,1,1), 365, #duration(1,0,0,0)),
    #"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Convertido para Tabela",{{"Column1", "Data"}}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Data", type date}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Tipo Alterado", "Data_id", each Date.ToText([Data], "yyyyMMdd"), type text),
    #"Ano Inserido" = Table.AddColumn(#"Personalização Adicionada", "Ano", each Date.Year([Data]), Int64.Type),
    #"Mês Inserido" = Table.AddColumn(#"Ano Inserido", "Mes", each Date.Month([Data]), Int64.Type),
    #"Semana do Mês Inserida" = Table.AddColumn(#"Mês Inserido", "Semana do Mês", each Date.WeekOfMonth([Data]), Int64.Type),
    #"Nome do Mês Inserido" = Table.AddColumn(#"Semana do Mês Inserida", "Nome do Mês", each Date.MonthName([Data]), type text)
in
    #"Nome do Mês Inserido"

 

 

 

DAX formula table Compet:

 

 

Compet = 'Calendar'

 

 

 

DAX Running Total Expected Tasks by Month:

 

 

 

Expected Tasks = CALCULATE(
    COUNT(Tasks[Task]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Mes] = MAX('Calendar'[Mes]) &&
        'Calendar'[Ano] = MAX('Calendar'[Ano])
    )
)

 

 

 

**EDIT**

Add a second image that does not load the 1st time.

**EDIT 2**

Add a second image that does not load the 2nd time.

**EDIT 3**

Add a second image that does not load the 3rd time.

2 Replies