Forum Discussion

jvandyck's avatar
jvandyck
Icon for Helper IV rankHelper IV
8 years ago
Solved

MTD and YTD

I have sales nrs for non continuous dates. For example sales person A sold 10 in 201712 and sales person B 100 in 201803.

My MTD needs to show by default 100 for sales person B and 0 for sales person A, assuming the current month is 201803. If I select month 201712, it needs to show 10 for sales person A and 0 for sales person B.

 

I have played around with a data dimension table and the MTDTOTAL function, but cannot get it working properly. Any help is much appreciated!

Need Help
  • jvandyck's avatar
    jvandyck
    8 years ago

    I added a date table;

    Date =
     ADDCOLUMNS (
    CALENDAR (DATE(2017;1;1); DATE(year(today());12;31));
     "YYYYMM"; FORMAT ( [Date]; "YYYYMM" );
     "Year"; YEAR ( [Date] );"Month";format([Date];"MM")
     )

     

    For the YYYYMM I added a column to calculate the current month: Month Selection = if(value('Date'[YYYYMM])=year(today())*100 + month(TODAY());"current month";'Date'[YYYYMM])

     

    I joined my fact tables with the new table and used the following to calculate YTD: aantal_WN YTD = TOTALYTD(sum(Payroll[aantal_WN]);'Date'[Date])

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi jvandyck,

     

    If you don't have a calculated column YearMonth, you could create it with this formula.

     

    YearMonth = YEAR('Sale'[date])*100+MONTH('Sale'[date])

    Then you could try to  create the measure below to get the MTD data. 

     

    Measure =
    IF (
        HASONEVALUE ( Sale[YearMonth] ),
        SUM ( Sale[sales] ),
        CALCULATE (
            SUM ( Sale[sales] ),
            FILTER (
                ALL ( Sale ),
                YEAR ( 'Sale'[date] ) = YEAR ( TODAY () )
                    && MONTH ( Sale[date] ) = MONTH ( TODAY () )
            )
        )
    )

     

    Hope it can help you!

     

    In addition, you could refer to this similar post.

     

    Best Regards,

    Cherry

    • jvandyck's avatar
      jvandyck
      Icon for Helper IV rankHelper IV
      8 years ago

      Thank you for your reply. However this cannot work, because the check on hasonevalue is true for both sales persons, so it will show the value for each sales person in my example.

      • jvandyck's avatar
        jvandyck
        Icon for Helper IV rankHelper IV
        8 years ago

        I added a date table;

        Date =
         ADDCOLUMNS (
        CALENDAR (DATE(2017;1;1); DATE(year(today());12;31));
         "YYYYMM"; FORMAT ( [Date]; "YYYYMM" );
         "Year"; YEAR ( [Date] );"Month";format([Date];"MM")
         )

         

        For the YYYYMM I added a column to calculate the current month: Month Selection = if(value('Date'[YYYYMM])=year(today())*100 + month(TODAY());"current month";'Date'[YYYYMM])

         

        I joined my fact tables with the new table and used the following to calculate YTD: aantal_WN YTD = TOTALYTD(sum(Payroll[aantal_WN]);'Date'[Date])