Forum Discussion

bilingual's avatar
bilingual
Icon for Helper V rankHelper V
6 years ago
Solved

PYTD as filter - best practice?

Hi i am going to use PYTD as a column in order to use it as a filter as i need it for different variables, can you help me in building up the formula? 

I have an existing formula for the last 12 months:

 

Last12 months= var daydiff=DATEDIFF('Rejsedata'[Date];TODAY();MONTH ) return
IF(daydiff>=0&&daydiff<=12;"Last12 months";"")

 

 

 

Kind regards Daniel

 

 

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi bilingual ,

     

    If you want to show PYTD between 2019/1/1 – 2020/6/1, please refer the following column:

     

    PYTD 1 = 
var _daydiff=DATEDIFF('Table'[Date],TODAY(),MONTH ) 
var _todayyear = DATE(YEAR(TODAY()),1,1)
var _difmonth = DATEDIFF(_todayyear,TODAY(),MONTH)
return
IF(_daydiff>=0 && _daydiff <= 12 + _difmonth,"PYTD","")

     

    If you want to show PYTD between 2019/1/1 – 2019/12/1, please refer the following column:

     

    PYTD 2 = 
var _daydiff=DATEDIFF('Table'[Date],TODAY(),MONTH ) 
var _todayyear = DATE(YEAR(TODAY()),1,1)
var _difmonth = DATEDIFF(_todayyear,TODAY(),MONTH)
return
IF(_daydiff>_difmonth && _daydiff <= 12 + _difmonth,"PYTD","")

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Here is one potential approach.  Just substitute your table [Date] name for 'Date'[Date].

     

    PYTD =
VAR __todayyear =
    YEAR ( TODAY () )
VAR __todayday =
    DATEDIFF ( DATE ( __todayyear, 1, 1 ), TODAY (), DAY ) + 1
VAR __thisrowday =
    DATEDIFF ( DATE ( YEAR ( 'Date'[Date] ), 1, 1 ), 'Date'[Date], DAY ) + 1
RETURN
    IF (
        __thisrowday <= __todayday
            && YEAR ( 'Date'[Date] ) = __todayyear - 1,
        "PYTD",
        ""
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • bilingual's avatar
      bilingual
      Icon for Helper V rankHelper V
      6 years ago

      Hi mahoney9pat, thanks a lot for your help, somehow the filter only works for the YTD last year, in my data it only selects Jan-Jun 2019, could you help me? "Rejsedata'[Den 1 dato i måned]" is the first day of the month for every month, as data is aggregated on months.

       

      PYTD =
      VAR __todayyear =
      YEAR ( TODAY () )
      VAR __todayday =
      DATEDIFF ( DATE ( __todayyear; 1; 1 ); TODAY (); DAY ) + 1
      VAR __thisrowday =
      DATEDIFF ( DATE ( YEAR ( 'Rejsedata'[Den 1 dato i måned] ); 1; 1 ); 'Rejsedata'[Den 1 dato i måned]; DAY ) + 1
      RETURN
      IF (
      __thisrowday <= __todayday
      && YEAR ( 'Rejsedata'[Den 1 dato i måned] ) = __todayyear - 1;
      "PYTD";
      ""
      )
      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi bilingual ,

         

        If you want to show PYTD between 2019/1/1 – 2020/6/1, please refer the following column:

         

        PYTD 1 = 
var _daydiff=DATEDIFF('Table'[Date],TODAY(),MONTH ) 
var _todayyear = DATE(YEAR(TODAY()),1,1)
var _difmonth = DATEDIFF(_todayyear,TODAY(),MONTH)
return
IF(_daydiff>=0 && _daydiff <= 12 + _difmonth,"PYTD","")

         

        If you want to show PYTD between 2019/1/1 – 2019/12/1, please refer the following column:

         

        PYTD 2 = 
var _daydiff=DATEDIFF('Table'[Date],TODAY(),MONTH ) 
var _todayyear = DATE(YEAR(TODAY()),1,1)
var _difmonth = DATEDIFF(_todayyear,TODAY(),MONTH)
return
IF(_daydiff>_difmonth && _daydiff <= 12 + _difmonth,"PYTD","")

         

         

        If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

        BTW, pbix as attached.

         

        Best regards,

         

        Community Support Team _ zhenbw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    bilingual , I am assuming it prior yeat till date. Use time intelligence for that with date calendar

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.