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 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!

2 Replies

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