Filtering using YTD and LYTD

[Anonymous]

Hello everyone,

We are trying to create following measure with DAX:

Actived_Customer (1/0): customer is actived when YTD Sales are higher than LYTD Sales.

We have following measures created:

ImporteA_YTD (YTD Sales) =

CALCULATE(

[ImporteA_Acum];

DATESBETWEEN(DimTiempo[Date];

DATE(YEAR([DateFilteredTo]);1;1);[DateFilteredTo])

)

ImporteA_LYTD (LYTD Sales) =

CALCULATE(

[ImporteA_Acum];

DATESBETWEEN(DimTiempo[Date];

DATE(YEAR([DateFilteredTo])-1;1;1);DATE(YEAR([DateFilteredTo])-1;MONTH([DateFilteredTo]);DAY([DateFilteredTo])))

)

We are trying following DAX code for the new measure, but it not works as expected.

DescCliente_Activo (Actived_Customer)=

CALCULATE(

DISTINCTCOUNT(DimCliente[DescCliente]);

FILTER(FactFacturaVenta; [ImporteA_YTD]>[ImporteA_LYTD])

)

The result is:

And the data model is:

Does somebody know how to create measure correctly?

Thanks in advanced!

[amitchandak]

Please try formula similar to these for YTD and LYTD. Make sure you created a separate date dimension

Year  = CALCULATE(SUM(table[column]),DATESYTD('Date'[Date Filer]))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),('Date'[Date Filer])))
Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),dateadd('Date'[Date Filer].-12,month)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak ,

Thanks for your help.

The problem we had was with the Actived_Customer measure, not with date measures.

Finally, we solved it changing measure applying FILTER function on Customer dimension instead of sales fact.

DescCliente_Activo (Actived_Customer)=

CALCULATE(

DISTINCTCOUNT(DimCliente[DescCliente]);

FILTER(FactFacturaVentaDimCliente; [ImporteA_YTD]>[ImporteA_LYTD])

)