Help needed with a DAX Formula

[SebasB]

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.

[SebasB]

PowerBI file in Google Drive 

[amitchandak]

@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-Calend...

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.

[SebasB]

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.