cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CharlesK Frequent Visitor
Frequent Visitor

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

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: DETAILED TIME INTELLIGENCE

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], -1DAY ) )

 

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 Frequent Visitor
Frequent Visitor

Re: DETAILED TIME INTELLIGENCE

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,380)