Forum Discussion

Giskard's avatar
Giskard
New Member
8 years ago
Solved

Comparing cummulative from different months by day

Hi guys!

I need to compare the evolution of sales (cummulativeday by day) vs the previous month day by day.

 

Using previousmonth what I get is the total of the previous month.

 

for example:

 

 

Is there a way to do so?

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Giskard

     

    Hi, please try with this little change:

     

    Sales Currenth Month =
VAR SelectedDay =
    SELECTEDVALUE ( Table1[Day of Month] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        FILTER (
            ALL ( Table1 );
            MONTH ( Table1[Date] ) = MONTH ( TODAY () )
                && Table1[Day of Month] <= SelectedDay
        )
    )
    Sales Previous Month =
VAR SelectedDay =
    SELECTEDVALUE ( Table1[Day of Month] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        FILTER (
            ALL ( Table1 );
            MONTH ( Table1[Date] )
                = MONTH ( TODAY () ) - 1
                && Table1[Day of Month] <= SelectedDay
        )
    )

    Regards

     

    Victor

    Lima - Peru

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Giskard,

     

    Please try measures:

    current month sales =
CALCULATE (
    SUM ( 'Table 2'[Cummulative Sales] ),
    FILTER ( 'Table 2', 'Table 2'[Date].[MonthNo] = MONTH ( TODAY () ) )
)

previous month sales =
CALCULATE (
    SUM ( 'Table 2'[Cummulative Sales] ),
    FILTER ( 'Table 2', 'Table 2'[Date].[MonthNo] = MONTH ( TODAY () ) - 1 )
)

     

    Best regards,

    Yuliana Gu

    • Giskard's avatar
      Giskard
      New Member
      8 years ago

      Thanks a lot Yuliana!

      Unfortunataly is not working.

       

      Im getting the values daily but is not summing them.

       

      Also I believe the TODAY part is the one causing I cant change the filter to other month.

       

      What I have is a main fact and a date dimension.

       

      I tried this measure:

       

      AcumuladoCET = CALCULATE(SUM(DetalleCanal[CET]),filter(DimDate,DimDate[MonthofYear]=MONTH(today())))

       

      I think thats what you did. What Im doing wrong?

       

      Thanks once again!

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Giskard

     

    Hi, please try with this little change:

     

    Sales Currenth Month =
VAR SelectedDay =
    SELECTEDVALUE ( Table1[Day of Month] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        FILTER (
            ALL ( Table1 );
            MONTH ( Table1[Date] ) = MONTH ( TODAY () )
                && Table1[Day of Month] <= SelectedDay
        )
    )
    Sales Previous Month =
VAR SelectedDay =
    SELECTEDVALUE ( Table1[Day of Month] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] );
        FILTER (
            ALL ( Table1 );
            MONTH ( Table1[Date] )
                = MONTH ( TODAY () ) - 1
                && Table1[Day of Month] <= SelectedDay
        )
    )

    Regards

     

    Victor

    Lima - Peru

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this measure

     

    =CALCULATE([Sales],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-1),EDATE(MIN('Calendar'[Date]),-1)))

     

    There is a relatiosnhip from the Date column of the Data Table to the Date column of the Calendar Table.  In the Pivot/visual, drag the date from the calendar table.

     

    Hope this helps.