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 t...
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

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