cancel
Showing results for
Did you mean:
Regular Visitor

## DAX measures to show particular dates only

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: DAX measures to show particular dates only

Hi @smileamile,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
New Contributor

## Re: DAX measures to show particular dates only

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

Regular Visitor

## Re: DAX measures to show particular dates only

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?

Highlighted
Community Support Team

## Re: DAX measures to show particular dates only

Hi @smileamile,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.