Can't get MTD to work with multiple figures for same days

[dajuju]

I have a table reporting sales per day for multiple stores (let's say 3 stores).

This gives me 3 sets of the same date value.

ex. table name = Sales

Date           Store      Sales

1/1/2019      A         5,000.00

1/1/2019     B          10,000.00

1/1/2019     C           7,500.00

1/2/2019     A           6,000.00

1/2/2019      B          11,000.00

1/2/2019      C          6,000.00

I have a separate date table connected to the sales table, for this example let's call it dDate.

The measures I've been using are:

SumSales = SUM(Sales[sales])

SalesMTD = TOTALMTD([SumSales], dDate[Date])

When I try to use the TOTALMTD formula (using the above example for 1/2/2019) it just spits out the sales for the day of 23,000.00 instead of the MTD total of 45,500.00.

Also when I use a slicer on a graph for the MTD sales on a per-store basis it also just gives me a graph that looks like the daily sales instead of the MTD for the store.

If I separate the stores into their own tables then these same measures work fine. It's only when they are combined into this summary table that I can't get the MTD to work properly.

Any suggestions please?

[CheenuSing]

Hi @dajuju 

From your description of the problem you seem to want YTD than MTD.  Have you tried TOTALYTD function.

Cheers

CheenuSing

[dajuju]

Hi @CheenuSing. I'm looking for MTD for each month, not YTD. 

I'm basically trying to compare the performance of each store to x day of the month each month.

[dajuju · Accepted solution]

In case someone else is searching for the same answer I figured it out.

I made another table that contained the date range that i wanted summation for and used the formulas shown on this page:

https://community.powerbi.com/t5/Desktop/how-to-create-sum-by-dates-column/m-p/199979#M87981

I made two columns, one for total sales (summed from the original table), then month to date based on the new total sales column.