cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdbhealy Visitor
Visitor

Date format

I have a data table that has transactions for a retail business. I am wanting to make a line chart which has each year as a separate line, and then the month/day as the horizontal axis with quantity sold as the vertical axis. Ultimately I would like the line to be a running total as of the date on the horizontal axis the quantity sold year to date.  

 

transactions.JPG

 

The first problem I am having is with getting just the month-day. I was able to separate out the year from the 'SaleDay' but any time I try to just get the month-day separate I keep getting errors. I am using DirectQuery to an sql database so as the database updates the chart will update as well. So some solutions I have found on the forum have not worked because it says it is not supported by directquery.

 

Chart.JPG

If anyone has a suggestion for also get a running total (the quick measure way didn't work for me, maybe I did wrong fields) I would be very appreciative. 

1 ACCEPTED SOLUTION

Accepted Solutions
vik0810 Member
Member

Re: Date format

Do You have predefined values for years? If so, you can create a measure for given years current total and place them on your chart.

A measure definition could be like that:

 

2016 = CALCULATE (
    [your measure],
    FILTER (
        ALL ( Table[SaleDay] ),
        MONTH(Table[SaleDay]) <= MAX ( MONTH(Table[SaleDay]) )
            && YEAR ( Table[SaleDay] ) = 2016
    )
)

and your result would look like:

 

rt.png

View solution in original post

1 REPLY 1
vik0810 Member
Member

Re: Date format

Do You have predefined values for years? If so, you can create a measure for given years current total and place them on your chart.

A measure definition could be like that:

 

2016 = CALCULATE (
    [your measure],
    FILTER (
        ALL ( Table[SaleDay] ),
        MONTH(Table[SaleDay]) <= MAX ( MONTH(Table[SaleDay]) )
            && YEAR ( Table[SaleDay] ) = 2016
    )
)

and your result would look like:

 

rt.png

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors