Grouping values into more than one group

[christopheralan]

I'm hoping there is a simple solution to this:

I've created a column chart with months in the x-axis and a total column in the y-axis. I now want to display multiple year-to-date (YTD) columns along the x-axis rather than individual months. For example, I'd like to see Feb YTD, Mar YTD, Apr YTD, May YTD, ..., Dec YTD on the x-axis. Groups seems to be the answer to this problem, but it looks like I can only add an ungrouped value to one group, not multiple groups. For example, I can only add January and February to the Feb YTD group and not to another group like Mar YTD. Essentially, it seems that values and groups are a 1-to-1 relationship, instead of a 1-to-many relationship.

Does someone know how to allow values to be grouped into multiple groups? Or is there some DAX expression for what I'm trying to do?

Thank you for your help!

[LaurentCouartou · Accepted solution]

By default, the engine does not handle n:n relationships, so DAX will be required to do a YTD calculation.

If you use April release, you may want to try the new quick measures feature preview:

https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/

If you need more help, don't hesitate to ask here (with a description of your model).

[christopheralan]

Thanks, LaurentCouartou!  Quick measures worked :).

[bblais]

Assuming you have a calendar table, you can first create a measure to SUM up your sales, or in my example Revenue

Revenue = SUM(Sales[Order Revenue])

Then just create a TOTALYTD measure that calculates revenue YTD based on your sale date or order date or whatever date:

Revenue YTD = TOTALYTD([Revenue],'Calendar'[Date])

Then if you group up your calendar date by month:

and make it your axis of your column chart, and make Revenue YTD your value, it should look like this:

[MFelix]

Hi @christopheralan,

For doing this you should calculate a measure with TOTALYTD then used that as your values in the visuals.

Example: YTDMEASURE = TOTALYTD (SUM(Table[columnvalue]), table1[date])

Use the date column as your x-axis, there is also available the QTD and MTD calculation.

Regards,
Mfelix