How to do cumulative line chart in power bi

[camelia_]

Hi, i am still new to power bi and i want to do cumulative line chart . Below is my data(from 1 jan to 30 june) and i need to do cumulative weekly/quarterly/monthly line chart based on country and ast. What i know for now is that in power bi can select only one column, not same as in excel can select for example from 1 jan to 1 feb. 

Is there any alternative ways to do? Thanks in advance.

[BA_Pete · Accepted solution]

HI @camelia_ ,

Import your table as it is into Power Query. Multi-select (Ctrl+click) your [COUNTRY] and [AST] columns.

Go to the Transform tab > Unpivot olumns (dropdown) > Unpivot other columns. Format your new date column as date type. Now you have a properly structured table to apply to your data model. I'll assume you leave your new date column called [Attribute], and your new values columns [Values] for the next bit.

You also need to create a proper calendar table - there's loads of resources online to help you do this, so I'll not go into the detail here. I'll assume that you call your calendar table 'calendar', and you relate it to your fact table on calendar[date] ONE > MANY yourFactTable[Attribute].

Create a measure on your fact table with the following code:

_valuesCumulativeSum =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    SUM(yourFactTable[Values]),
    FILTER(
        ALLSELECTED(calendar),
        calendar[date] <= __cDate
    )
)

Just use this measure in a visual that has calendar[date] on the axis and this should give you what you need.

Pete

[camelia_]

hi @BA_Pete , thanks for the solution now i am able to do the cumulative line chart. Is just that why I get the same for all country? The chart suppose to have different line for each country.

And also, i want to do weekly cumulative line chart, i try to use this

 Week Number = WEEKNUM ( 'Calendar'[Date] )

but it can't detect the date in the calendar table.

[BA_Pete]

Hi @camelia_ ,

First thing: in your screenshot, you have used the [Attribute] column as your chart axis. This should be calendar[date] here. Your cumulative measure references the calendar table, so you must use this same field in the visual to apply the correct context to the measure. You should also mark your calendar table as the date table for your model. In the fields list, click the ellipsis next to 'Calendar' and you will see an option to 'Mark as Date Table'. Select this and then choose the [date] column from the dropdown list when prompted. Other than this, I can't say why you would be getting the same values for every country. If the above doesn't resolve this, it may be a data issue as there's nothing in the measure that would affect the country aspect.

Regarding using the WEEKNUM for the axis, you will need to add this as a column in your calendar table.

Create a new DAX calculated column on you calendar table like this:

weekNumber = WEEKNUM(calendar[date])

You can then use this column on the visual axis and it will work fine.

Pete