MTD and YTD

[jvandyck]

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!

[v-piga-msft]

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]

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 · Accepted solution]

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])