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

Current day sum vs previous day sum

Hi,

 

Very new to Power BI, so any help is appreciated.

 

I have a database table that has fields Category, SubCategory, Value and ReportDate. The Value is not a sales figure, it's a point-in-time valuation (think stock value on a given day). This gets populated every day, so I have rows with contiguous dates and the value changing each day.

 

I'm looking to make a table report that has Category, Value Today, Value 30 Days Ago and Value 90 Days Ago (these value columns need to be summed by category). Delta columns would be an added bonus. All the data required for something like this is in the table I think but I'm not sure how to approach it.

 

I have created a Date table (like here http://community.powerbi.com/t5/Desktop/how-to-calculate-month-over-month-sales/m-p/63647/highlight/...) hoping it would help but I'm not getting anywhere.

 

I've tried adding these measures to the report, but they are both the same value (and are also the same as just adding my [Value] column).

CurrentDayValue = CALCULATE(SUM(ValuationTable[Value]), DATEADD(DateTable[Date], 0, MONTH))

30DayValue = CALCULATE(SUM(ValuationTable[Value]), DATEADD(DateTable[Date], -30, DAY))

 

If I don't display my measures and just show [Category] and [Value] and add a filter on the ReportDate, I can see all my dates and selecting just 1 date results in a value I expect. I can also then just filter on a previous date and see the correct value for that date, but I want these to be side by side in the report.

 

Hope this makes sense. Can anyone help?

 

Cheers,
Ciaran

1 ACCEPTED SOLUTION

Accepted Solutions
ciarano Frequent Visitor
Frequent Visitor

Re: Current day sum vs previous day sum

I think I have something that works.

Would be interested in hearing feedback if this is a good approach or if there's a better way:

 

Previous Value = 
CALCULATE (
    SUM (ValuationTable[Value]),
        FILTER (
            ALL (ValuationTable),
            COUNTROWS (
                FILTER (
                    ValuationTable,
                    EARLIER (ValuationTable[ReportDate]) = DATEADD(ValuationTable[ReportDate], -30, DAY)
                        && EARLIER (ValuationTable[Category]) = ValuationTable[Category]
                 )
            )
      )
)

View solution in original post

2 REPLIES 2
ciarano Frequent Visitor
Frequent Visitor

Re: Current day sum vs previous day sum

I think I have something that works.

Would be interested in hearing feedback if this is a good approach or if there's a better way:

 

Previous Value = 
CALCULATE (
    SUM (ValuationTable[Value]),
        FILTER (
            ALL (ValuationTable),
            COUNTROWS (
                FILTER (
                    ValuationTable,
                    EARLIER (ValuationTable[ReportDate]) = DATEADD(ValuationTable[ReportDate], -30, DAY)
                        && EARLIER (ValuationTable[Category]) = ValuationTable[Category]
                 )
            )
      )
)

View solution in original post

Highlighted

Current day vs previous day

Hi Ciarano,

 

I am having transaction amount, Status and date in my data. wanted to show the value with indicator showing green (if value is greater than previous day) or red (if it is lesser than previos day). Please help me on the same.

 

Thanks,

Anupam

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors