DAX measures to show particular dates only

[Anonymous]

I have a fact table with a Quantity measure that has some quantities for every day up to yesterday.

I need to have a line chart that tracks current and previous months Quantity by day for beginning of month through yesterday. (Example below) When I use the MTD calculation, it is a running total rather than the actual quantity per day.

What's the best way to go about this? Calculated measures for current month (Nov 1-12) and prior month (Oct 1-12)? How would I write those?

Sample data below: 

My data - all I want returned is what's in the chart above (created in Excel, need to recreate similar in power bi). So I only care about 10/1-10/12 and 11/1-11/12 data. I still need it broken up by day, not the full MTD total.

factSales table
Date	Quantity
10/1/2018	17696
10/2/2018	31072
10/3/2018	26309
10/4/2018	25934
10/5/2018	34095
10/6/2018	17907
10/7/2018	18546
10/8/2018	21664
10/9/2018	25757
10/10/2018	22143
10/11/2018	23938
10/12/2018	32196
10/13/2018	22000
10/14/2018	31512
10/15/2018	15885
10/16/2018	5643
10/17/2018	9100
10/18/2018	20000
10/19/2018	25000
10/20/2018	21000
10/21/2018	35455
10/22/2018	21002
10/23/2018	21004
10/24/2018	36985
10/25/2018	75412
10/26/2018	11112
10/27/2018	11587
10/28/2018	14568
10/29/2018	24556
10/30/2018	24587
10/31/2018	23510
11/1/2018	33296
11/2/2018	16544
11/3/2018	18232
11/4/2018	16015
11/5/2018	14005
11/6/2018	14200
11/7/2018	19414
11/8/2018	19252
11/9/2018	21209
11/10/2018	18874
11/11/2018	19697
11/12/2018	18051

[Anonymous]

Possible to add some sample data?  Posts with that generally get more answers. 

[Anonymous]

More details added above. It's essentially a Month over Month comparison by day. I need to limit the amounts on the chart to Oct 1-12 and Nov 1-12 from my Quantity measure.

I think this works for current month:  CALCULATE(SUM(factSalesTPD[QuantityShipped]),filter('Date','Date'[Date]<=today()-1 && 'Date'[Date]>=DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1)))

But I'm not sure how to do previous month. And what happens when we're at the end of the month and I can't compare the 31st to 30th? Will it just be blank or error out?

[v-piga-msft · Accepted solution]

Hi @Anonymous,

For your requirement, you could try the two measures below.

Oct =
VAR a =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) - 1 )
VAR b =
    STARTOFMONTH ( 'Table1'[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Quantity] ),
        DATESBETWEEN ( 'Table1'[Date], b, a ),
        FILTER ( 'Table1', MONTH ( 'Table1'[Date] ) = MONTH ( TODAY () ) - 1 )
    )
Nov =
CALCULATE (
    SUM ( Table1[Quantity] ),
    DATESBETWEEN ( 'Table1'[Date], STARTOFMONTH ( 'Table1'[Date] ), TODAY () - 1 ),
    FILTER ( 'Table1', MONTH ( 'Table1'[Date] ) = MONTH ( TODAY () ) )
)

Then you could create a calculated column with Day() function and use Day column as Axis and use Oct, Nov as values to create the line chart like below.

More details, you could refer to my attachment.

Best Regards,

Cherry