topic Re: Obtain calculated columns for current year, current month, last year in App Development

Obtain calculated columns for current year, current month, last year

alespooletto — Wed, 21 Feb 2024 15:18:42 GMT

The table I am working with is very simple, it literally has only the name of the office, and the sum of its orders in total.

I want to make some columsn that give me, the Net IO for the whole year until now, for the current month ,and for the current year. Are the functions YTD() available in power BI usable in the same way here? And if they do, how can I change the name of the column to show it's only for the specific period of time?

Thank you!

Re: Obtain calculated columns for current year, current month, last year

LRuCelver — Wed, 21 Feb 2024 15:28:20 GMT

Assuming the current Date is the max. Date you can use these set analyses to create the values:

YTD: {<DateField={">=$(=YearStart(DateField))"}>}

PYTD: {<DateField={">=$(=YearStart(AddYears(DateField, -1))) <$(=YearStart(DateField))"}>}

MTD: {<DateField={">=$(=MonthStart(DateField))"}>}

Re: Obtain calculated columns for current year, current month, last year

alespooletto — Wed, 21 Feb 2024 15:29:55 GMT

Thank you for the response. Is it possible to put these formulas directly into the Load Editor script?

Re: Obtain calculated columns for current year, current month, last year

LRuCelver — Wed, 21 Feb 2024 15:35:53 GMT

No. Set Analysis can only be used if charts.

If you want to calculate these measures in the script you would have to aggregate by the year/month and only keep the relevant results.

Re: Obtain calculated columns for current year, current month, last year

alespooletto — Wed, 21 Feb 2024 15:50:49 GMT

Thanks for the information.

I am trying to use the information you sent me, it seems like there is an issue.

If I understand correctly, I changed the DateField with the one I want, and the YearStart is a function, but it seems like this one won't work. It gives me this error:

Thanks for the patience