topic Re: Help needed with a DAX Formula in DAX Commands and Tips

Help needed with a DAX Formula

SebasB — Thu, 17 Dec 2020 08:46:50 GMT

The "problem" is as following:
I want a new column that calculates with several statements, for instance:
(Started with the saving of data from 01-2020 thats why I start there)

From Jan-2020 t/m okt-2020 use table: "Amount" (decimal number) * table "Cost price 2020"(decimal number);
From Nov-2020 t/m okt-2021 use table: "Amount"(decimal number) * table "Cost price 2021"(decimal number);
From Nov-2021 t/m okt-2022 use table: "Amount"(decimal number) * table "Cost price 2022"(decimal number);

I want the calculations column in my table "doorbelasting", and I did make a date table with DAX that has several Columns like Date (relation with Date in table "doorbelasting", Quarter, Year, YearMonthNumber, etc.).

Date =

ADDCOLUMNS (

CALENDAR (DATE(2020,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

Tried several things but keep getting errors, and also kind of dont want to make extra date columns in the table "doorbelasting" to keep it as clean as possible.

Re: Help needed with a DAX Formula

amitchandak — Thu, 17 Dec 2020 10:43:18 GMT

SebasB , is your date table working. If not

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

for the above one you can try

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"10/31"))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Help needed with a DAX Formula

SebasB — Thu, 17 Dec 2020 12:25:05 GMT

Hi Amit,

Yeah this one is working, works fine with all views. Uploaded data.

Did not yet add all data, will be some data from ServiceNow and Azure added but those are not needed for this part.

Re: Help needed with a DAX Formula

SebasB — Thu, 17 Dec 2020 12:27:46 GMT

PowerBI file in Google Drive