Forum Discussion

jmolina's avatar
jmolina
Frequent Visitor
4 years ago

Previous Year to Date until certain Date.

Hello everyone, along with saying hello, I hope you can help me with the following:   I have a dataset that has several million pieces of data, from which I need to calculate the YTD and Previous Y...
Need Help
jmolina's avatar
jmolina
Frequent Visitor
4 years ago

Ashish, hi, I have tried your formulas and the same thing still happens to me, when selecting a retailer that has its total sales from 2022 until 06-04-2022, the formula for SPLY total sales continues to consider until 06-05-2022 when comparing, it should show only until 06-04-2022.

 

HOW IS:

 

HOW IT SHOULD BE:

 

Please help

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

Try this

New measure = if(isblank([Total sales]),blank(),[Total sales in SPLY])

Hope this works.

  • jmolina's avatar
    jmolina
    Frequent Visitor
    4 years ago

    Ashish, this work perfect!, but im still having a little issue, how do i do the same for month to date and  previuos month to date??? i try to simulate the New Mesaure for MTD and PMTD without suceed, It must meet the same conditions as the formula you gave me.

     

    Thanks in advance!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      What measures have you written?

      • jmolina's avatar
        jmolina
        Frequent Visitor
        4 years ago

        Ashish, i have this measures:

         

        #MTD = CALCULATE([TOTAL SALES],
        DATESBETWEEN(Calendar[Date],
        FIRSTDATE(DATEADD(Calendar[Date],0,MONTH)),
        IF(MONTH(MIN(Calendar[Date])) = MONTH(MAX(Calendar[Date])),EDATE(MAX(Calendar[Date]),0), LASTDATE(DATEADD(Calendar[Date],0,MONTH)))))

         

        #MTD-1 = IF([TOTAL SALES] = BLANK(), BLANK(),
        CALCULATE([Ventas Selecc],
        DATESBETWEEN(Calendar[Date],
        FIRSTDATE(DATEADD(Calendar[Date],-12,MONTH)),
        IF(MONTH(MIN(Calendar[Date])) = MONTH(MAX(Calendar[Date])),EDATE(MAX(Calendar[Date]),-12), LASTDATE(DATEADD(Calendar[Date],-12,MONTH))))))

         

        The MTD-1 It must meet the same conditions as the "New Measure" you gave me for the Previous YTD.