How to differentiate completed months and non completed months in Power BI

[Anonymous]

Hi Team,

I have got a requirement to show line chart with data points shown below . Please help me with this. 

Month	Vulnerabilities	Week Ending
August	38374	5
September	32345	5
October	377	4

I have complete set of data (i.e full 5 weeks ) for August and September as those are previous months. However, October is still current month and current week is 4 . 

So, the data points for line chart should be 38374 , 32345 and 377 (until the week changes for october) .if week changes and data sums up to 3839(example) , the data points for line chart should be 38374 , 32345 and 3839 . 

I am unable to show most current weeks data for current month and complete data for previous months . Kindly help . 

As of now, I have created a Week Number based out of weekending to just display last week's data . However,its not working for me . 

[Greg_Deckler]

Can you post sample source data? I assume that what you presented is what you want displayed? So that I understand, if you are in a non-completed month, you want to display only the most recent week data but if a month is completed, you want the sum of all of the weeks of data for that month, correct?

[Anonymous]

Hi Greg,

You are almost there. Exactly, so when I am in a non-completed month, i am looking for most recent week data and if I am in previous months , i am looking for sum of all the weeks of data. Below is data sample . 

Date	VP	Director	Manager	# Assets	Critical	High	Medium	Low	# Missing Patches
8/31/2018				4654645	6000	54654	456468	654614	1171736
8/31/2018				1695415	20000	316561	65487	261226	663274
8/31/2018				654347	16456	2513653	546511	26062	3102682
8/31/2018				546456	95654	213123	46536	85645	440958
8/31/2018				268753	45687	23212	165032	545665	779596
8/31/2018				654646	143358	31246	123456	6578	304638
8/31/2018				868461	785465	23165	5000	1651	815281
9/30/2018				4654650	5498	54654	456468	654614	1171234
9/30/2018				1695420	20100	316561	65487	261226	663374
9/30/2018				654357	16456	2513653	546511	26062	3102682
9/30/2018				546456	92039	213123	46536	85645	437343
9/30/2018				268753	44409	23212	165032	545665	778318
9/30/2018				654646	143321	31246	123456	6578	304601
9/30/2018				868461	785465	23165	5000	1651	815281
10/24/2018				4654645	6000	54654	456468	654614	1171736
10/24/2018				1695415	20000	316561	65487	261226	663274
10/24/2018				654347	16456	2513653	546511	26062	3102682
10/24/2018				546456	95654	213123	46536	85645	440958
10/24/2018				268753	45687	23212	165032	545665	779596
10/24/2018				654646	143358	31246	123456	6578	304638
10/24/2018				868461	785465	23165	5000	1651	815281

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I made one sample for your reference, please check the steps as below.

1. Create a Calenda table and create calculated columns in the data table. Here we should create relationship between fact table and the calenda table.

Calenda = CALENDARAUTO()

weekend = IF(ISBLANK([Measure]),BLANK(),CALCULATE(MAX(Calenda[weekmonth]),ALLEXCEPT(Calenda,Calenda[Month])))

weekmonth = 1+WEEKNUM(Calenda[Date])-WEEKNUM(STARTOFMONTH(Calenda[Date]))

2. To create some calculated columns in the fact table.

Month = FORMAT(Table1[Date],"mmmm")

week = 1+WEEKNUM(Table1[Date])-WEEKNUM(STARTOFMONTH(Table1[Date]))

3. Create the measures as below.

Measure = var total = CALCULATE(SUM(Table1[# Missing Patches]))
var maxweek = CALCULATE(MAX(Table1[week]),FILTER(Table1,Table1[Month]=FORMAT(TODAY(),"mmmm")))
return 
IF(MAX(Calenda[Month])=FORMAT(TODAY(),"mmmm"),CALCULATE(SUM(Table1[# Missing Patches]),FILTER(Table1,Table1[week]=maxweek)),total)

weekend = IF(ISBLANK([Measure]),BLANK(),CALCULATE(MAX(Calenda[weekmonth]),ALLEXCEPT(Calenda,Calenda[Month])))

For more details, please chekc the pbix as attached.

Regards,

Frank

[Anonymous]

Hi Frank,

There is little tweak in the requirement and I tried to explain the issue below .

As of now, the formulaes you have created are summing up for the entire month , but I am just looking for a week . and that week should be like this. 

- For August , we just have one record(row) and that is 31-Aug ,we should get #Missing Vulnerabilities equal to 469,065

- For September, last week data should be summed up . which should be equal to 259,176 . (26th Aug week) 

- For October , Last week is 24th oct week and data should be summed up which is equal to 459,809 . 

So , ideally we are looking at just last weeks data in each month . Could you please help me in modifying those as I am new to Power BI ?

Dataset : 

Date	VP	Director	Manager	# Assets	Critical	High	Medium	Low	# Missing vulnerabilities
31-Aug	VP	Director	Manager	78,394	154,093	229,006	68,563	16,141	469,065
12-Sep	VP	Director	Manager	2,785	2,246	2,787	1,430	24	6,487
12-Sep	VP	Director	Manager	2,522	7,826	42,939	33,418	6,552	91,997
12-Sep	VP	Director	Manager	24,000	59,884	55,110	51	0	115,045
12-Sep	VP	Director	Manager	27,000	78,238	109,959	4	0	188,201
12-Sep	VP	Director	Manager	3,622	0	6	25,362	7,244	32,612
12-Sep	VP	Director	Manager	496	552	671	6,664	721	8,608
12-Sep	VP	Director	Manager	17,969	5,347	17,534	1,634	1,600	26,115
19-Sep	VP	Director	Manager	3,083	12,836	3,572	2,735	0	21,097
19-Sep	VP	Director	Manager	3,043	5,683	18,742	17,912	6,800	48,694
19-Sep	VP	Director	Manager	23,334	32,637	38,581	0	400	71,618
19-Sep	VP	Director	Manager	27,000	78,238	109,959	4	0	188,201
19-Sep	VP	Director	Manager	3,622	0	0	25,368	7,248	32,616
19-Sep	VP	Director	Manager	1,640	434	1,560	3,760	1,301	7,055
19-Sep	VP	Director	Manager	20,691	5,424	19,038	1,533	1,504	27,499
19-Sep	VP	Director	Manager	0	0	0	0	0	0
19-Sep	VP	Director	Manager	0	0	0	0	0	0
19-Sep	VP	Director	Manager	0	0	0	0	0	0
19-Sep	VP	Director	Manager	0	0	0	0	0	0
26-Sep	VP	Director	Manager	3,114	4,510	743	1,795	0	14,143
26-Sep	VP	Director	Manager	3,108	6,789	22,482	21,341	6,997	57,609
26-Sep	VP	Director	Manager	23,451	63,065	34,103	0	55	120,674
26-Sep	VP	Director	Manager	30,045	37	66	0	0	119
26-Sep	VP	Director	Manager	3,622	0	0	25,368	7,248	32,616
26-Sep	VP	Director	Manager	1,640	293	1,291	3,396	1,326	6,306
26-Sep	VP	Director	Manager	20,691	5,424	19,038	1,533	1,504	27,499
26-Sep	VP	Director	Manager	0	0	0	0	0	0
26-Sep	VP	Director	Manager	0	0	0	0	0	0
26-Sep	VP	Director	Manager	144	73	136	1	0	210
26-Sep	VP	Director	Manager	0	0	0	0	0	0
3-Oct	VP	Director	Manager	2,986	3,837	649	1,585	1	13,169
3-Oct	VP	Director	Manager	3,079	6,015	19,915	18,698	5,936	50,564
3-Oct	VP	Director	Manager	23,451	63,065	34,103	0	55	120,674
3-Oct	VP	Director	Manager	30,213	41,783	14,556	0	0	56,339
3-Oct	VP	Director	Manager	3,622	0	0	25,368	7,248	32,616
3-Oct	VP	Director	Manager	1,640	428	344	1,655	1,194	3,621
3-Oct	VP	Director	Manager	20,752	5,377	16,173	1,533	1,552	24,635
3-Oct	VP	Director	Manager	0	0	0	0	0	0
3-Oct	VP	Director	Manager	0	0	0	0	0	0
3-Oct	VP	Director	Manager	144	73	136	1	0	210
3-Oct	VP	Director	Manager	0	0	0	0	0	0
10-Oct	VP	Director	Manager	3,071	3,081	534	1,349	0	12,295
10-Oct	VP	Director	Manager	3,093	5,515	18,265	17,676	5,161	46,617
10-Oct	VP	Director	Manager	23,472	64,015	33,547		53	121,249
10-Oct	VP	Director	Manager	30,286	3,625	6,656	2	0	10,283
10-Oct	VP	Director	Manager	3,622	0	3,630	21,838	3,644	29,112
10-Oct	VP	Director	Manager	1,640	254	335	1,883	1,197	3,669
10-Oct	VP	Director	Manager	20,752	5,377	16,173	1,533	1,552	24,635
10-Oct	VP	Director	Manager	0	0	0	0	0	0
10-Oct	VP	Director	Manager	0	0	0	0	0	0
10-Oct	VP	Director	Manager	148	13	71	0	0	84
10-Oct	VP	Director	Manager	0	0	0	0	0	0
17-Oct	VP	Director	Manager	3,066	5,143	945	2,868	0	8,956
17-Oct	VP	Director	Manager
17-Oct	VP	Director	Manager	23,408	44,599	13,895	0	0	143,164
17-Oct	VP	Director	Manager	30,833	49,051	13,710	12	0	62,773
17-Oct	VP	Director	Manager	3,622	0	3,630	21,838	3,644	29,112
17-Oct	VP	Director	Manager	1,682	322	541	2,307	3,735	6,905
17-Oct	VP	Director	Manager	20,822	508	8,692	47	15	9,262
17-Oct	VP	Director	Manager
17-Oct	VP	Director	Manager
17-Oct	VP	Director	Manager	177	13	236	0	0	249
17-Oct	VP	Director	Manager
24-Oct	VP	Director	Manager	3,062	4,044	591	1,869	0	6,504
24-Oct	VP	Director	Manager	3,112	4,754	16,564	14,740	3,773	39,831
24-Oct	VP	Director	Manager	23,433	90,713	36,097	0	0	283,023
24-Oct	VP	Director	Manager	31,768	75,283	19,970	9	0	95,262
24-Oct	VP	Director	Manager	3,627	0	3,630	21,838	3,644	29,112
24-Oct	VP	Director	Manager	1,692	301	539	2,263	233	3,336
24-Oct	VP	Director	Manager	20,673	496	1,731	82	52	2,361
24-Oct	VP	Director	Manager
24-Oct	VP	Director	Manager	44	89	0	42	0	131
24-Oct	VP	Director	Manager	177	13	236	0	0	249
24-Oct	VP	Director	Manager

[Anonymous]

@v-frfei-msft @Greg_Deckler - Any thing on the below posted Question mates ?

[Anonymous]

Hi Frank,

Thanks for sending those detailed formulaes and helping me out. I am in process of creating calendar table and got struck with 3rd calculated field "Weekend" 

weekend = IF(ISBLANK([Measure]),BLANK(),CALCULATE(MAX(Calenda[weekmonth]),ALLEXCEPT(Calenda,Calenda[Month])))

The "[Measure]" thats used in the formulae is not being recognised. I am getting below error . i dont understand what is expected over there, Could you please help me with that . 

Error: 

[Anonymous]

Frank, Sorry , I didnt checked the measures below. I acheived what i am looking for . Thanks for your Quickest solution . 

Thanks,

G Venkatesh