Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtering using YTD and LYTD

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 creat...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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])
    )