Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
dtango9
Helper II
Helper II

Need FISCAL YTD formula + Slicer

Untitled.jpg

 

Hello,


I created a dashboard and it is almost complete!  I'm just looking to edit the calculated measure shown below so that it represents Fiscal Year-to-Date (that begins Dec 1 to Nov 30 every year), instead of just the last 12 months.

 

YTD Actuals = CALCULATE ( SUM ( 'finance ExpensesWithAccountDescriptions '[ Total Amount ] ), DATESINPERIOD ( ' New Calender Table '[ Dates ], today () , -1 , YEAR ))

 

Also, the "today()" portion is not syncing with the slicer.  The data remains the same no matter what date I select on the slicer, even though the other columns all make the adjustments.

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @dtango9 ,

According to your description, the data from October 1, 2019 to December 31, 2020 is created:

v-yangliu-msft_0-1607318387467.png

 

Here are the steps you can follow:

1. Create Measure.

Measure =
CALCULATE(SUM('Table'[amount]),FILTER(ALLSELECTED('Table'),[Date]<=TODAY()&&[Date]>=DATE(YEAR('Table'[Date])-1,MONTH(TODAY()),DAY(TODAY()))))

2. Result.

v-yangliu-msft_1-1607318387472.png

Your measure filter does not generate you. You can add allselect() or all() functions to the function. For this kind of content, you can check the related link:

https://docs.microsoft.com/en-us/dax/all-function-dax

https://docs.microsoft.com/en-us/dax/allselected-function-dax

 

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @dtango9 ,

According to your description, the data from October 1, 2019 to December 31, 2020 is created:

v-yangliu-msft_0-1607318387467.png

 

Here are the steps you can follow:

1. Create Measure.

Measure =
CALCULATE(SUM('Table'[amount]),FILTER(ALLSELECTED('Table'),[Date]<=TODAY()&&[Date]>=DATE(YEAR('Table'[Date])-1,MONTH(TODAY()),DAY(TODAY()))))

2. Result.

v-yangliu-msft_1-1607318387472.png

Your measure filter does not generate you. You can add allselect() or all() functions to the function. For this kind of content, you can check the related link:

https://docs.microsoft.com/en-us/dax/all-function-dax

https://docs.microsoft.com/en-us/dax/allselected-function-dax

 

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

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

amitchandak
Super User
Super User

@dtango9 , Dateytd can take end date of year

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"11/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"11/30"))

 

 

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"11/30"),filter('Date', format('Date'[Date],"MMDD")<=_max))


YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"11/30")), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date],"11/30"),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date],"11/30"),filter('Date','Date'[Date]<=_max))

 

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year),"11/30"),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year),"11/30"),'Date'[Date]<=_max)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors