Forum Discussion

sg919's avatar
sg919
Frequent Visitor
2 years ago
Solved

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 AM

 

"DAX help"
Calendar Table
Date cal

1 Reply