DETAILED TIME INTELLIGENCE

[CharlesK]

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 but please help.

so far i've been able to get YTD like this

YTD Sales =
   
    CALCULATE( [Manual Sales] , DATESYTD('Trading Calendar'[Dates].[Date]  ))

but this is what i need. i dont want my users to have to manually pick date ranges. so i want
 

1) Yesterday Sales

2) Same Day yesterday last year
3) Week to date
4) Same Week to date Last year
5) Month To date
6) Same month to date Last year

7) Same YTD previous year. 

i've searched and found several that jus didnt work.

for yesterday i tried

Yesterday Sales =

CALCULATE([Sales],
   
    YEAR(Sales[Date])=DATE(TODAY(),today(),today()-1))

for MTD i've tried

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

i jus cant seem to get it.

Please help.

[v-yuta-msft]

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]

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.