Forum Discussion

CharlesK's avatar
CharlesK
Frequent Visitor
8 years ago

DETAILED TIME INTELLIGENCE

Time intelligence is soooooooooooooo frustrtating by soooooooo powerful.....   Can't seem to get anything except YTD to work.    I am kinda new to PBI and have alot to learn regarding context etc...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

Hi CharlesK,

 

Based on your description, [Sales] is a measure you have created, right?

 

Please use DAX formula like this and try again.

Yesterday Sales =
CALCULATE ( [Sales], Sales[Date] >= DATEADD ( Sales[Date], -1, DAY ) )

 

MTD =
IF (
    HASONEVALUE ( 'Trading Calendar'[Deacons Year] )
        && HASONEVALUE ( 'Trading Calendar'[Month Num] ),
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Trading Calendar' ),
            MONTH ( 'Trading Calendar'[Dates] )
                = MONTH ( EARLIER ( 'Trading Calendar'[Dates] ) )
        )
    ),
    BLANK ()
)

 

Regards,

Jimmy Tao

 

 

 

CharlesK's avatar
CharlesK
Frequent Visitor
8 years ago

Hi Jimmy,

 

thank you for your response.

 

Yes [Sales] is a measure i've created to convert all sales pulled from ERP into once currency.

Yesterday formula you have given me returns this error :(

"A function 'DATEADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed." 

The MTD formula returns

"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

I wish DAX formulas were as intuitive and language coherent as excel formulas.