Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am need to adjust my QTD and YTD formulas. I need to only calculate for previous months. Example, if I'm refreshing the file in July we are only reporting on January - June. Our data pulls in future month values which I don't want to see in these calcs.
These are my current formulas. These are intended for matrix tables that have no date axis. These are working great so I'd like to keep these and just add in the argument to start the calculation the month prior.
YTD Actual =
CALCULATE (
SUM ( 'vCorporateGandA'[Amount] ),
FILTER (
ALL ( 'CalendarTable' ),
YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )
&& 'CalendarTable'[Date] <= TODAY ()
),
'vCorporateGandA'[LedgerType] = "Actual"
)
QTD Actual =
CALCULATE (
SUM ( 'vCorporateGandA'[Amount] ),
FILTER (
ALL ( 'CalendarTable' ),
YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )
&& QUARTER ( 'CalendarTable'[Date] ) = QUARTER ( TODAY ())
&& 'CalendarTable'[Date] <= TODAY ()
),
'vCorporateGandA'[LedgerType] = "Actual"
)
@brittanyruffin , Try like
YTD =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
Last Qtr =
var _max1 = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _max = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,Month(_max1)))
var _min = eomonth(_min,-3)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |