cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
smileamile Regular Visitor
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: 

 

example.PNG

 

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 
DateQuantity
10/1/201817696
10/2/201831072
10/3/201826309
10/4/201825934
10/5/201834095
10/6/201817907
10/7/201818546
10/8/201821664
10/9/201825757
10/10/201822143
10/11/201823938
10/12/201832196
10/13/201822000
10/14/201831512
10/15/201815885
10/16/20185643
10/17/20189100
10/18/201820000
10/19/201825000
10/20/201821000
10/21/201835455
10/22/201821002
10/23/201821004
10/24/201836985
10/25/201875412
10/26/201811112
10/27/201811587
10/28/201814568
10/29/201824556
10/30/201824587
10/31/201823510
11/1/201833296
11/2/201816544
11/3/201818232
11/4/201816015
11/5/201814005
11/6/201814200
11/7/201819414
11/8/201819252
11/9/201821209
11/10/201818874
11/11/201819697
11/12/201818051

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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.

 

output.PNG

 

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
Nick_M New Contributor
New Contributor

Re: DAX measures to show particular dates only

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

smileamile Regular Visitor
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?

Community Support Team
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.

 

output.PNG

 

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.