Forum Discussion

Ale's avatar
Ale
Icon for Resolver II rankResolver II
4 years ago
Solved

YTD value as Calculated Column (not measure) in Data View

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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

  • Ale's avatar
    Ale
    Icon for Resolver II rankResolver II
    4 years ago

    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?