Column graph of last months with line over for current month.

[Snip]

Hi, I have been looking for a solution to my problem for a while. 

I have a grafh of the last 12 months, where the data in each much is from the 1 of the month to current date. 

I could not figure out how to do this calculation in Power BI so i did in SQL. So my data is like below, where we asume it is the 2020-03-02. 

Date	Amount	Customer
2020-01-01	10	x
2020-01-02	5	x
2020-01-01	58	y
2020-01-02	9	y
2020-02-01	1	x
2020-02-02	15	x
2020-02-01	5	y
2020-02-02	8	y
2020-03-01	25	x
2020-03-02	4	x
2020-03-01	3	y
2020-03-02	7	y

Now i want a graf where the x-axis is January and febuary column and the column for januar would be 85 and for February 29. I would then like a line over both column having the total value of march, which is 39. 

It should then be possible to have a sliver on the customer type.

I have added a picture of the visualization, but i can not get it to work with by numbers. 

Can somebody pelase help me? 

[Greg_Deckler]

@Snip Sorry, having trouble following, can you post sample data as text and expected output?

Are you saying that if today is September 23rd that September should show the summed value from September 1st to September 23rd but August should show from August 3rd to September 23rd and July should show from July 1st to September 23rd?

"where the data in each much is from the 1 of the month to current date."

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Snip]

Hi Greg_Deckler

I thought i have explained it very thoroughly but i can try again. I have only an example of 2 days in each month in the table because it was easier than having from the first of the month to the current date for 13 months in a table. 

But i would to to add a text example of my data, but it is bot possible for me, so i put it as a table. The data is from 1st to 20rd of the last 13 months. But not all dates from 1st to 20rd is there, because it is only if the customer bought something that date, this also mean that there is not the same amount of days in each month. 

I then would like a graph as in my post for the last 12 months before this month, so August-December 2019 and January-August 2020 as columns and then September 2020 as a straight line on top of the columns.  

Hope this make more sense and some body can help me. 

DATE  VALUE CUSTOMER

2019-08-01 281.6 x
2019-08-01 142 y
2019-08-02 214.6 x
2019-08-02 109.6 y
2019-08-03 1.3 y
2019-08-04 0.1 y
2019-08-05 221.7 x
2019-08-05 122.9 y
2019-08-06 268.8 x
2019-08-06 133.3 y
2019-08-07 207.5 x
2019-08-07 120.3 y
2019-08-08 243.8 x
2019-08-08 107.2 y
2019-08-09 223.7 x
2019-08-09 122.1 y
2019-08-10 0.3 x
2019-08-10 1.7 y
2019-08-11 0.1 y
2019-08-12 185 x
2019-08-12 107.6 y
2019-08-13 222.6 x
2019-08-13 113.7 y
2019-08-14 267.1 x
2019-08-14 113.1 y
2019-08-15 245.3 x
2019-08-15 130.4 y
2019-08-16 250.5 x
2019-08-16 115.1 y
2019-08-17 0.2 x
2019-08-17 1 y
2019-08-19 186.4 x
2019-08-19 118.8 y
2019-08-20 264.9 x
2019-08-20 133.7 y
2019-09-01 0.2 x
2019-09-02 180.1 x
2019-09-02 113.7 y
2019-09-03 238 x
2019-09-03 178.6 y
2019-09-04 226.5 x
2019-09-04 129.1 y
2019-09-05 231.8 x
2019-09-05 111.9 y
2019-09-06 222.2 x
2019-09-06 116.6 y
2019-09-07 0.7 x
2019-09-07 1.3 y
2019-09-08 0.1 y
2019-09-09 179.1 x
2019-09-09 122.3 y
2019-09-10 251.2 x
2019-09-10 136.1 y
2019-09-11 233.1 x
2019-09-11 126.3 y
2019-09-12 248.9 x
2019-09-12 127.7 y
2019-09-13 250.9 x
2019-09-13 122.8 y
2019-09-14 0.3 x
2019-09-14 1.6 y
2019-09-15 0.1 x
2019-09-15 3.8 y
2019-09-16 203.6 x
2019-09-16 110.4 y
2019-09-17 260 x
2019-09-17 128.1 y
2019-09-18 204.3 x
2019-09-18 133.9 y
2019-09-19 242.8 x
2019-09-19 109.9 y
2019-09-20 195.7 x
2019-09-20 122.2 y
2019-10-01 278.5 x
2019-10-01 135.5 y
2019-10-02 224.6 x
2019-10-02 124.6 y
2019-10-03 206.4 x
2019-10-03 116.4 y
2019-10-04 228.9 x
2019-10-04 121.1 y
2019-10-05 0.5 x
2019-10-05 2.8 y
2019-10-07 175.1 x
2019-10-07 130.1 y
2019-10-08 293.5 x
2019-10-08 172.3 y
2019-10-09 238.5 x
2019-10-09 137.2 y
2019-10-10 210.9 x
2019-10-10 128 y
2019-10-11 198.4 x
2019-10-11 127.6 y
2019-10-12 0.1 x
2019-10-12 1.6 y
2019-10-13 0.3 y
2019-10-14 180.2 x
2019-10-14 118.9 y
2019-10-15 249.7 x
2019-10-15 156.7 y
2019-10-16 209 x
2019-10-16 114.5 y
2019-10-17 207.6 x
2019-10-17 117.2 y
2019-10-18 177.1 x
2019-10-18 102 y
2019-10-19 0.1 x
2019-10-19 1 y
2019-10-20 0.1 y
2019-11-01 224.8 x
2019-11-01 125.7 y
2019-11-02 0.2 x
2019-11-02 0.6 y
2019-11-04 204 x
2019-11-04 117.6 y
2019-11-05 248.2 x
2019-11-05 116 y
2019-11-06 237.9 x
2019-11-06 127.6 y
2019-11-07 250 x
2019-11-07 146 y
2019-11-08 243.3 x
2019-11-08 131.3 y
2019-11-09 0.5 x
2019-11-09 1.7 y
2019-11-10 0.1 x
2019-11-10 0.2 y
2019-11-11 206.2 x
2019-11-11 126.8 y
2019-11-12 273.6 x
2019-11-12 158.4 y
2019-11-13 221.8 x
2019-11-13 135.8 y
2019-11-14 222.7 x
2019-11-14 131.6 y
2019-11-15 230.1 x
2019-11-15 134.6 y
2019-11-16 0.3 x
2019-11-18 187.3 x
2019-11-18 124.7 y
2019-11-19 293.1 x
2019-11-19 125.9 y
2019-11-20 280.1 x
2019-11-20 150.7 y
2019-12-01 0.6 x
2019-12-01 0.3 y
2019-12-02 322 x
2019-12-02 148.4 y
2019-12-03 352.7 x
2019-12-03 166.7 y
2019-12-04 277.2 x
2019-12-04 150.7 y
2019-12-05 256.6 x
2019-12-05 146.3 y
2019-12-06 225.9 x
2019-12-06 132.4 y
2019-12-07 0.1 x
2019-12-07 0.2 y
2019-12-08 0.5 x
2019-12-09 223.7 x
2019-12-09 124.9 y
2019-12-10 264.5 x
2019-12-10 144.1 y
2019-12-11 215.1 x
2019-12-11 130.3 y
2019-12-12 270.3 x
2019-12-12 164.2 y
2019-12-13 236.5 x
2019-12-13 142.4 y
2019-12-14 0.5 x
2019-12-16 217 x
2019-12-16 157.4 y
2019-12-17 303.3 x
2019-12-17 163 y
2019-12-18 261.5 x
2019-12-18 129.2 y
2019-12-19 268.2 x
2019-12-19 151.5 y
2019-12-20 278.7 x
2019-12-20 147.4 y
2020-01-01 2.1 x
2020-01-01 0.8 y
2020-01-02 170.3 x
2020-01-02 66 y
2020-01-03 186.8 x
2020-01-03 73 y
2020-01-04 0.1 x
2020-01-04 0.1 y
2020-01-05 0.1 x
2020-01-06 165.1 x
2020-01-06 97.4 y
2020-01-07 271.8 x
2020-01-07 126.4 y
2020-01-08 245.2 x
2020-01-08 149.1 y
2020-01-09 216.9 x
2020-01-09 147.9 y
2020-01-10 197 x
2020-01-10 153.7 y
2020-01-11 0.8 y
2020-01-13 216.8 x
2020-01-13 157 y
2020-01-14 270.8 x
2020-01-14 148.5 y
2020-01-15 262.4 x
2020-01-15 143.8 y
2020-01-16 198.9 x
2020-01-16 146 y
2020-01-17 223.4 x
2020-01-17 141.8 y
2020-01-18 0.5 x
2020-01-18 0.5 y
2020-01-20 186.5 x
2020-01-20 114 y
2020-02-01 2.1 x
2020-02-02 9.6 x
2020-02-03 210.2 x
2020-02-03 133.6 y
2020-02-04 260.9 x
2020-02-04 147.5 y
2020-02-05 263.2 x
2020-02-05 155.9 y
2020-02-06 284.8 x
2020-02-06 156.4 y
2020-02-07 223 x
2020-02-07 120.7 y
2020-02-08 0.5 x
2020-02-08 0.1 y
2020-02-09 2.9 x
2020-02-10 213 x
2020-02-10 108.1 y
2020-02-11 316 x
2020-02-11 158.2 y
2020-02-12 250.7 x
2020-02-12 115.2 y
2020-02-13 242.9 x
2020-02-13 140.4 y
2020-02-14 215.7 x
2020-02-14 138.7 y
2020-02-15 0.8 x
2020-02-16 2.2 x
2020-02-16 0.2 y
2020-02-17 218.9 x
2020-02-17 124.7 y
2020-02-18 280.3 x
2020-02-18 142 y
2020-02-19 224.9 x
2020-02-19 145.8 y
2020-02-20 245.7 x
2020-02-20 136.2 y
2020-03-01 3.2 x
2020-03-01 0.2 y
2020-03-02 219.3 x
2020-03-02 114.9 y
2020-03-03 327.4 x
2020-03-03 158.2 y
2020-03-04 282.5 x
2020-03-04 140.1 y
2020-03-05 258 x
2020-03-05 146.5 y
2020-03-06 270.7 x
2020-03-06 143.5 y
2020-03-07 1.9 x
2020-03-07 0.2 y
2020-03-08 7 x
2020-03-09 228.5 x
2020-03-09 131.6 y
2020-03-10 301 x
2020-03-10 180 y
2020-03-11 302.6 x
2020-03-11 150.2 y
2020-03-12 270.9 x
2020-03-12 138 y
2020-03-13 287.9 x
2020-03-13 140.7 y
2020-03-14 1 x
2020-03-15 5.5 x
2020-03-15 0.1 y
2020-03-16 253.9 x
2020-03-16 140.1 y
2020-03-17 352.1 x
2020-03-17 171.4 y
2020-03-18 331.2 x
2020-03-18 168.6 y
2020-03-19 294.6 x
2020-03-19 177.5 y
2020-03-20 294.6 x
2020-03-20 169.3 y
2020-04-01 344.5 x
2020-04-01 141.1 y
2020-04-02 411.4 x
2020-04-02 139.3 y
2020-04-03 388.6 x
2020-04-03 149.3 y
2020-04-04 3.4 x
2020-04-04 0.1 y
2020-04-05 1 x
2020-04-06 346.5 x
2020-04-06 138.7 y
2020-04-07 409.5 x
2020-04-07 144.2 y
2020-04-08 403.2 x
2020-04-08 141.2 y
2020-04-09 17.5 x
2020-04-09 3.7 y
2020-04-10 3.4 x
2020-04-10 0.6 y
2020-04-12 0.5 x
2020-04-13 2.2 x
2020-04-13 0.1 y
2020-04-14 394 x
2020-04-14 146.2 y
2020-04-15 395 x
2020-04-15 159.7 y
2020-04-16 507.6 x
2020-04-16 140.3 y
2020-04-17 414.3 x
2020-04-17 162.7 y
2020-04-18 12.8 x
2020-04-19 8.1 x
2020-04-20 512.6 x
2020-04-20 163.8 y
2020-05-01 450.7 x
2020-05-01 140.2 y
2020-05-02 2.5 x
2020-05-03 8.9 x
2020-05-04 425.9 x
2020-05-04 110.1 y
2020-05-05 493.5 x
2020-05-05 144.6 y
2020-05-06 415.1 x
2020-05-06 164.9 y
2020-05-07 408.8 x
2020-05-07 135.9 y
2020-05-08 6.4 x
2020-05-08 0.1 y
2020-05-09 5.3 x
2020-05-10 11.3 x
2020-05-11 521 x
2020-05-11 148.8 y
2020-05-12 375 x
2020-05-12 176.9 y
2020-05-13 491.4 x
2020-05-13 158.8 y
2020-05-14 472.5 x
2020-05-14 127.3 y
2020-05-15 391.9 x
2020-05-15 136.1 y
2020-05-16 6.8 x
2020-05-17 5.7 x
2020-05-18 452.8 x
2020-05-18 127.3 y
2020-05-19 463.5 x
2020-05-19 161.4 y
2020-05-20 458.9 x
2020-05-20 130.2 y
2020-06-01 1.6 x
2020-06-01 0.8 y
2020-06-02 293.9 x
2020-06-02 142.5 y
2020-06-03 388.4 x
2020-06-03 161.9 y
2020-06-04 521.5 x
2020-06-04 144.1 y
2020-06-05 11.8 x
2020-06-05 2.2 y
2020-06-07 0.5 x
2020-06-08 423.9 x
2020-06-08 153.8 y
2020-06-09 437.6 x
2020-06-09 158.6 y
2020-06-10 454 x
2020-06-10 158.3 y
2020-06-11 404.7 x
2020-06-11 141 y
2020-06-12 397.1 x
2020-06-12 136.5 y
2020-06-13 0.1 x
2020-06-14 3.6 x
2020-06-14 0.1 y
2020-06-15 297.9 x
2020-06-15 129.3 y
2020-06-16 464.5 x
2020-06-16 150 y
2020-06-17 381.6 x
2020-06-17 128.7 y
2020-06-18 376.1 x
2020-06-18 139.1 y
2020-06-19 403.2 x
2020-06-19 132.9 y
2020-06-20 0.4 x
2020-06-20 0.1 y
2020-07-01 369.9 x
2020-07-01 120.7 y
2020-07-02 324.3 x
2020-07-02 128.7 y
2020-07-03 320.6 x
2020-07-03 116.6 y
2020-07-04 1.5 x
2020-07-06 297.9 x
2020-07-06 114.1 y
2020-07-07 352.4 x
2020-07-07 125.1 y
2020-07-08 317.7 x
2020-07-08 105.3 y
2020-07-09 300.4 x
2020-07-09 123.1 y
2020-07-10 309.5 x
2020-07-10 117.8 y
2020-07-11 2.4 x
2020-07-11 0.1 y
2020-07-13 242 x
2020-07-13 114.1 y
2020-07-14 338.8 x
2020-07-14 104.4 y
2020-07-15 275 x
2020-07-15 108.3 y
2020-07-16 294.2 x
2020-07-16 108.2 y
2020-07-17 241.6 x
2020-07-17 115.4 y
2020-07-18 0.3 x
2020-07-20 225.8 x
2020-07-20 74.3 y
2020-08-01 0.6 x
2020-08-03 181.8 x
2020-08-03 101.3 y
2020-08-04 282.7 x
2020-08-04 94.5 y
2020-08-05 262.7 x
2020-08-05 106.1 y
2020-08-06 294.3 x
2020-08-06 103 y
2020-08-07 262.6 x
2020-08-07 109.1 y
2020-08-08 0.7 x
2020-08-09 0.1 x
2020-08-10 223.9 x
2020-08-10 110.6 y
2020-08-11 361 x
2020-08-11 119.1 y
2020-08-12 303.3 x
2020-08-12 98.1 y
2020-08-13 287.7 x
2020-08-13 117.8 y
2020-08-14 265.1 x
2020-08-14 110.8 y
2020-08-15 2.1 x
2020-08-15 15 y
2020-08-17 228.3 x
2020-08-17 83.2 y
2020-08-18 283.1 x
2020-08-18 109.7 y
2020-08-19 297.5 x
2020-08-19 104.5 y
2020-08-20 283.2 x
2020-08-20 110.3 y
2020-09-01 327.1 x
2020-09-01 136.6 y
2020-09-02 285.6 x
2020-09-02 144.4 y
2020-09-03 300.8 x
2020-09-03 114.6 y
2020-09-04 277 x
2020-09-04 148.7 y
2020-09-05 0.5 x
2020-09-06 0.1 x
2020-09-06 1.2 y
2020-09-07 228.6 x
2020-09-07 119.7 y
2020-09-08 318.1 x
2020-09-08 111.6 y
2020-09-09 293 x
2020-09-09 111.6 y
2020-09-10 294.7 x
2020-09-10 114.7 y
2020-09-11 257.6 x
2020-09-11 132.7 y
2020-09-12 0.5 x
2020-09-12 0.3 y
2020-09-13 0.1 y
2020-09-14 269.1 x
2020-09-14 109.8 y
2020-09-15 322.2 x
2020-09-15 120.3 y
2020-09-16 278.6 x
2020-09-16 111.6 y
2020-09-17 298.4 x
2020-09-17 115.4 y
2020-09-18 284.9 x
2020-09-18 107.8 y
2020-09-19 0.5 x
2020-09-19 0.1 y

[v-deddai1-msft]

Hi @Snip ,

Would you please try to use line and clustered column chart? First create a new column for X-axis and  a new column for sort:

Month-Year = FORMAT ('Table'[DATE], "mmmm" ) &"-"&YEAR('Table'[DATE])
Column for sort = MONTH('Table'[DATE])/100+YEAR('Table'[DATE])

Sort the Month-Year column by Column for sort:

Create two measure one for column value and the other one for line value:

Measure = SUM('Table'[VALUE])
linemeasure = CALCULATE(SUM('Table'[VALUE]),FILTER(ALLSELECTED('Table'),MONTH('Table'[DATE]) = MONTH(TODAY()) &&YEAR('Table'[DATE])=YEAR(TODAY())))

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_aA_sLzxxChlY4_D...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Snip]

Hi Dedmon Dai

Thank you very much for your solution! 

But I have a problem and that is that the data, is related to a date table. 

I your example there is a table for the dates, but you do not use it to make the measures. 

So when i make the measures using the date table i only get a dot for the current month and not a line as you do. 

Hope you can help me  🙂 

Best regards Snip

[v-deddai1-msft]

Hi @Snip ,

I have updated the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_aA_sLzxxChlY4_D...  for using date table.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Snip]

Hi @v-deddai1-msft 

Thank you very much again! It helps me very much! 🙂

The only problem i have now is that the date table include more months than the current graph do. This is because I have another table where there is some different 
 months in. 
Can you help me with this problem as well? 🙂 

Best regards Snip

[v-deddai1-msft]

Hi @Snip ,

You can change the linemeasure to :

linemeasure = var a = CALCULATETABLE(VALUES('Table'[DATE]),ALL('Table')) return IF(MAX('Table 2'[Date]) in a,CALCULATE(SUM('Table'[VALUE]), ALLSELECTED('Table'),FILTER(ALL('Table 2'[Date]),MONTH('Table 2'[DATE]) = MONTH(TODAY()) &&YEAR('Table 2'[DATE])=YEAR(TODAY()))),BLANK())

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Snip]

Hi @v-deddai1-msft 

This do not work. it returns a measure for september only, not for all months. 

Do you have an expample where it works? 

[v-deddai1-msft]

Hi @Snip ,

Please refer to https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EU_aA_sLzxxChlY4_D...

Because today is 2020/10/2, so I change the measure in sample pbix to 

linemeasure = var a = CALCULATETABLE(VALUES('Table'[DATE]),ALL('Table')) return IF(MAX('Table 2'[Date]) in a,CALCULATE(SUM('Table'[VALUE]), ALLSELECTED('Table'),FILTER(ALL('Table 2'[Date]),MONTH('Table 2'[DATE]) = 9 &&YEAR('Table 2'[DATE])=YEAR(TODAY()))),BLANK())

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Snip]

Hi @v-deddai1-msft 

Now it has a line for all the months, but it also have a line for the months not included in table 1 with only the data. 

My date table has a longer period because of other data in my project. 

[v-deddai1-msft]

Hi @Snip ,

Have you check my sample pbix? I have set if before calculate.

Best Regards,

Dedmon Dai

[Snip]

Hi @v-deddai1-msft 

I can ses you make a measure 2 with some date, but you do not use it any where.

When i open the file it do not have the line in the graph. Can you maybe explain in details what i need to do? 

[v-deddai1-msft]

Hi @Snip ,

Sorry , I have uploaded wrong verison of pbix. You can try : https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EV4cu995filEjDZ9Ib... And I have set  

var a = CALCULATETABLE(VALUES('Table'[DATE]),ALL('Table')) return IF(MAX('Table 2'[Date]) in a

before the calculate to show blank() on date in calendar table not in fact table. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Snip]

 Hi @v-deddai1-msft 

I see now, but it still not working for me, it is now back at only calculating for september. 

In yours it is not possible not to have a filter on? så that it shows both customer x and y and have the line.