Forum Discussion

NewbieJono's avatar
NewbieJono
Post Partisan
4 years ago
Solved

if statement within another formula

Could someone help, i wish to add a statement within this dax (Please see bold section) to check Min date if another coloumn labeled 'FACT - ISG'[Counted]) <> "Not Counted"
 
also is anyone able to format this in the correct format for DAX formulas, mine is starting to get messy, i am not sure how best lay things out.
 
Working Days Old =
CALCULATE(
COUNTROWS('DIM - Date Table'),
DATESBETWEEN('DIM - Date Table'[Date], Min('FACT - ISG'[Date of Receipt]), max('FACT - ISG'[Date])),
'DIM - Date Table'[IsWorkingDay] = 1,
'DIM - Date Table'[IsHoliday] = 0,
all ('DIM - Date Table'))
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Try:

    Working Days Old =
VAR StartDate =
    CALCULATE (
        MIN ( 'FACT - ISG'[Date of Receipt] ),
        'FACT - ISG'[Counted] <> "Not Counted"
    )
VAR EndDate =
    MAX ( 'FACT - ISG'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'DIM - Date Table' ),
        DATESBETWEEN ( 'DIM - Date Table'[Date], StartDate, EndDate ),
        'DIM - Date Table'[IsWorkingDay] = 1,
        'DIM - Date Table'[IsHoliday] = 0
    )
RETURN
    Result

     

    I've removed all ('DIM - Date Table')). If you mark the table as a date table that is taken care of for you.

     

    Formatting wise download DAX Studio and it'll do it for you or use DAX Formatter by SQLBI

1 Reply

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Try:

    Working Days Old =
VAR StartDate =
    CALCULATE (
        MIN ( 'FACT - ISG'[Date of Receipt] ),
        'FACT - ISG'[Counted] <> "Not Counted"
    )
VAR EndDate =
    MAX ( 'FACT - ISG'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'DIM - Date Table' ),
        DATESBETWEEN ( 'DIM - Date Table'[Date], StartDate, EndDate ),
        'DIM - Date Table'[IsWorkingDay] = 1,
        'DIM - Date Table'[IsHoliday] = 0
    )
RETURN
    Result

     

    I've removed all ('DIM - Date Table')). If you mark the table as a date table that is taken care of for you.

     

    Formatting wise download DAX Studio and it'll do it for you or use DAX Formatter by SQLBI