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.
I'm trying to create 2 columns with the Year to Date values for each record in my table, but keeping all other descriptive data (Category, Product, etc) into account.
I attached a table with the example below. I have a data with 4 columns (Date, Category, Product, Value). So, for each Category and Product, I want to calculate the YTD for each of the rows of the table. The same logic will be then applied to a second column, but taking previous month YTD instead of current month.
Any help on that?
Solved! Go to Solution.
Hi, @Ale ;
Try to create a column by dax.
YTD =
CALCULATE(SUM('Table'[Value]),FILTER('Table',[Category]=EARLIER('Table'[Category])&&[Product]=EARLIER('Table'[Product])), DATESYTD('Table'[Date]))
YTD-M1 =
CALCULATE(MAX('Table'[YTD]),FILTER('Table',[Category]=EARLIER('Table'[Category])&&[Product]=EARLIER('Table'[Product])&&EOMONTH([Date],0)=EOMONTH(EARLIER('Table'[Date]),-1)))+0
The final show;
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Ale ;
Try it.
YTD-M1 =
CALCULATE (
MAX ( 'Table'[YTD] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Product] ),
EOMONTH ( [Date], 0 ) = EOMONTH ( EARLIER ( 'Table'[Date] ), -1 )
)
) + 0
The 30 descriptive Data (not only Category and Product) you said need to be input one by one. The descriptive data (descriptive data) can not be supported by all the columns at once.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Ale ;
Try to create a column by dax.
YTD =
CALCULATE(SUM('Table'[Value]),FILTER('Table',[Category]=EARLIER('Table'[Category])&&[Product]=EARLIER('Table'[Product])), DATESYTD('Table'[Date]))
YTD-M1 =
CALCULATE(MAX('Table'[YTD]),FILTER('Table',[Category]=EARLIER('Table'[Category])&&[Product]=EARLIER('Table'[Product])&&EOMONTH([Date],0)=EOMONTH(EARLIER('Table'[Date]),-1)))+0
The final show;
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-yalanwu-msft ,
your solution works fine for this dummy file. However, in my real world case, I have a table with more than 30 descriptive data (not only Category and Product). In that case, I presume I would have to write down all the columns of my table in the DAX.
Is there any way we could get this logic working for all columns without having to type them one by one?
@Ale , Try like
YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
YTD +1 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+2
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
ISFILTERED in a calculated column? I dont think so
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
91 | |
81 | |
65 | |
65 | |
60 |
User | Count |
---|---|
170 | |
114 | |
102 | |
73 | |
69 |