Monthly total in Line and column stacked chart

[jatneerjat]

Hi,

In the picture below i have present user count ,previous user count and date.

Black -> present user count

Yellow -> previous user count

So I want only 2555k in black(present user count) for july and then in august ie next month I want this 2555k in yellow(previous user count) and 3229k in black(present user count) then in september i want sum of 2555k and 3229k as previous user count and

3999k as present user count and so on.

so how can i get this?currently i am getting below: 

[v-lili6-msft]

hi, @jatneerjat

After my test, you can try to do these as below:

Step1:

Add a present user count measure

present = CALCULATE(SUM(Table1[Qty]))

Step2:

Add a year month number column

Year Month Number = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] ) 

Step3:

Add a previous user count measure

previous = CALCULATE(SUM(Table1[Qty]),FILTER(ALL('Date'),'Date'[Year Month Number]<MAX('Date'[Year Month Number])))

Step4:

Drag the fields into visual and set present measure is not blank

here is pbix, please try it.

https://www.dropbox.com/s/3wcw1haesubq5ns/Monthly%20total%20in%20Line%20and%20column%20stacked%20cha...

Best Regards,

Lin

[jatneerjat]

Hi @v-lili6-msft

Could you please remove the chart line and stacked column chart from your reply?

[v-lili6-msft]

hi, @jatneerjat

I had removed the chart line and stacked column chart from my reply. and all the data is I did my own sample data.

could you please mark the helpful replies as Answered?

Best Regards,

Lin

[jatneerjat]

i @v-lili6-msft

Thank you for your reply.I want exactly what you have shared in your pbix,but currently i am not getting.

I have created same measures mentioned by you and i used date column which is available in my table itself but it gives me different values for present july and previous august.

But i have only this values available for present measure

Why i am getting different value in august ?

When i use the column 'Year Month Number' then it also it gives different value

When i add date in visual filter i am not getting date hierarchy.

Can you help me how i can get what you have done in your pbix?

[v-lili6-msft]

hi, @jatneerjat

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[jatneerjat]

Hi @v-lili6-msft,

Thank you for your solution.It solved my problem.Can you remove that chart from your reply ?

[v-lili6-msft · Accepted solution]

hi, @jatneerjat

  When i add date in visual filter i am not getting date hierarchy.

It seems that you drag the date field into X-axis from the basic table, it should from date table

For example:

when I drag date field from date table

And whether there are any other slicers apply it?

If it is not your case, please share your sample pbix for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin

[Greg_Deckler]

Can you share some sample/example source data? Also, what aggegrations/calculations you are currently using would help as well. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490