WTD,MTD,YTD Measures using Date Calender Starting with feb and ending with Jan

[Anonymous]

Report Contains Two tables
1) Calender Table
2) Fact Table 
In Calender Table we have date,day,month,year , Week starting date, Week Ending Date ,Month Starting Date ,Month ending date , Year ending Date: Columns are available

 

In fact table we have sales_sls,cost,units and Remaining Columns are available 

In the Report the fiscal Year Starts with Feb and Ends with Jan-30 
Week Start by sunday and week end by Saturday .
Coming to the Report I need to Show Week to date ,Month to date & Year to date for Sales Column 
Report has to show till Last Week  data (in present week whatever be the day sun,mon,tue,....saturday) in all the WTD,MTD,YTD Measures.

Suggest the Ideas to solve the Issue.

[RManikyam]

Hi Amit, How to filter data till last week .. can you suggest for year column -- 

Here year starts with feb and end by jan ..

Week start by sunday and end by saturday...

now we are in Jan 8 th , In this report .. it has to show the report till 1st jan(from dec 26 th to 1st jan)

yr sales => YTD = calculate(SUM(tbl_ladder_rpt_refresh_bkp_mkd[ty_sls])/1000,datesytd(vw_cal_ext_lu[cal_dt],"1/31"))

Data is comin till Jan 8 .

How to filter data till last week that will be (from dec 26 th to 1st jan).

Could you please help me on this Amit.

[v-zhangti · Accepted solution]

Hi, @RManikyam @Anonymous 

If the date table starts on February 1 of the fiscal year. About the financial week can be set up like this.

Column:

Weekday = WEEKDAY('Date'[Date],1)

fiscal_week = Var N1 = CALCULATE(COUNT('Date'[Weekday]),FILTER('Date',[Weekday]=1&&[Date]<=EARLIER('Date'[Date]))) Return IF(N1=BLANK(),1,N1+1)

Is this the date you want to export the week?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@Anonymous , For calendar refer , if needed

1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

week

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

In case you need week to start from feb only

Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3-5-Power-BI/ba-p/1187502

WOW using RANK

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Calendar file attcahed if needed

[RManikyam]

Hi Amit, How to filter data till last week .. can you suggest for year column -- 

Here year starts with feb and end by jan ..

Week start by sunday and end by saturday...

now we are in Jan 8 th , In this report .. it has to show the report till 1st jan(from dec 26 th to 1st jan)

yr sales => YTD = calculate(SUM(tbl_ladder_rpt_refresh_bkp_mkd[ty_sls])/1000,datesytd(vw_cal_ext_lu[cal_dt],"1/31"))

Data is comin till Jan 8 .

How to filter data till last week that will be (from dec 26 th to 1st jan).

Could you please help me on this Amit.