date cal
1 Topic
Create Formula using Custom Calendar_Table
Hello - Appreciate any assistance on this! I have a custom Calendar_Table, where our FY starts in June. The Sale_Date column from the Sales_Table has a "relationship" established with the Calendar_Table Date column. In the Sales_Table I want to create a formula to calculate cost based on the FY FH column in my Calendar_Table however I am getting the following error with this code: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column. Cost = IF('Calendar_Table'[FY FH] = "FY24 H1", calculate(((90593*1.3)+(7549*4))*0.5, BLANK())) CALENDAR_TABLE DAX Calendar_Table = VAR _startdate = EDATE(TODAY(),-60) // this goes back 60 months from today VAR _enddate = EDATE(TODAY(),24) // if you want future dated records use EDATE(TODAY(),12) it'll put you 12 months into the future // you can mix and match all the columns as you need RETURN ADDCOLUMNS( CALENDAR(_startdate,_enddate), "Calendar Year", YEAR([Date]), // Calendar Year //"Calendar Quarter", "Q" & QUARTER([Date]), // CY Qtr //"Calendar Month", MONTH([Date]), // CY Month nbr //"Calendar Week", WEEKNUM([Date],1), // CY Week nbr "Day", DAY([Date]), // CY Day nbr //"Day Name", FORMAT([Date],"dddd"), // CY Day name "Year Month Name", YEAR([Date]) & " " & FORMAT([Date], "mmmm"), // CY Year and Month Name "Year Month Number", year([Date])&FORMAT(month([Date]),"00"), // CY Year month nbr "Month Name", FORMAT([Date],"mmmm"), // CY Month Name "FY", if(MONTH([Date])>= 6, YEAR([Date])+1, YEAR([Date])), // Fiscal Year "FQ", "Q" & QUARTER(EDATE([Date],-5)), // FY Qtr //"FM", MONTH(EDATE([Date],-5)), //FY Month nbr //"Fiscal Week", WEEKNUM(EDATE([Date],-5)), // FY Week nbr "FY FQ", if(MONTH([Date])>= 6, YEAR([Date])+1, YEAR([Date])) &" "&"Q" & QUARTER(EDATE([Date],-5)), // eg 2019 Q4 "FY FQ2", "FY"&RIGHT(if(MONTH([Date])>= 6, YEAR([Date])+1, YEAR([Date])),2) &" "&"Q" & QUARTER(EDATE([Date],-5)), // eg FY19 Q4 "FY FH","FY"&RIGHT(if(MONTH([Date])>= 6, YEAR([Date])+1, YEAR([Date])),2) &" "&if(MONTH([Date])>= 6 && MONTH([Date])<= 11,"H1","H2"), "FH TEST",if(MONTH([Date])>= 6 && MONTH([Date])<= 11,"H1","H2") ) SALES_TABLE (tied to DATE in Calendar_Table in PBI relationships) sale_dt 3/31/2022 12:00:00 AM 7/31/2023 12:00:00 AM 3/4/2022 12:00:00 AM 5/2/2022 12:00:00 AMSolved488Views0likes1Comment