PYTD as filter - best practice?

[bilingual]

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

[amitchandak]

@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-Y...


Appreciate your Kudos.

[mahoneypat]

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]

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 · Accepted solution]

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.

[bilingual]

Thanks a lot for your help, PYTD1 was just what i needed!