Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Kind regards Daniel
Solved! Go to 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 , 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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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.
Thanks a lot for your help, PYTD1 was just what i needed!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |