Help to Calculate Previous and Current Fiscal Periods

[st-dat]

Hello there,

I have my sales table related to Calendar table but

I need help to Calculate the TOTAL SALES during the period below: 

1) Last Week

2) Current Week To Date

3) Last Fiscal Month

4) Current Month To Date

5) Last Fiscal Quarter

6) Current Fiscal Quarter todate

7) Last Fiscal Year

😎 Current Fiscal Year To date

Thanks for your usual help

[Ashish_Mathur]

Hi,

1. Share some data to work with
2. What is your Fiscal Year
3. How would you define weeks?

[st-dat]

Hi,

Thanks for your response,

1) You could please use any test data available you, reason because I have no access to copy the fact table because I'm a volunteer.

2) The Fiscal year starts April 1, and ends March 31.

3) Sunday is the first day of the week and Saturday is the last day.

Best Regards

[st-dat]

Hi,

Let me know if I have been able to provide you required information to help solve my issue.

Your response will be highly appreciated.

Best Regards

[Ashish_Mathur]

Hi,

As requested, please share some data.  I do not want to create any dataset of my own.

[st-dat]

Hi Ashish,

I hope this sample dataset would be useful..

Calculate customer Net payment during the period listed: WTD, PrevW, MTD, PrevMonth, FiscalQTD, PrevFiscalQ, FYTD, and PrevFiscalYr

Many thanks

Invoice Number	Customer ID	Trans Date	Net Value
9992586	Customer 3813	12/14/2015	$      3,091.00
9992587	Customer 3814	12/21/2015	$      3,090.00
9992798	Customer 4025	11/02/2015	$      2,813.00
9992799	Customer 4026	11/09/2015	$      2,812.00
9992800	Customer 4027	10/30/2015	$      2,811.00
9992801	Customer 4028	10/23/2015	$      2,810.00
88917	Customer 4272	09/01/2015	$      2,451.00
88915	Customer 4273	09/08/2015	$      2,449.00
88912	Customer 4274	08/31/2015	$      2,446.00
88911	Customer 4275	08/24/2015	$      2,445.00
85519	Customer 4441	07/02/2015	$      2,132.00
85520	Customer 4442	07/09/2015	$      2,131.00
85572	Customer 4494	06/01/2015	$      1,976.00
85573	Customer 4495	06/08/2015	$      1,975.00
85574	Customer 4496	05/22/2015	$      1,973.00
85575	Customer 4497	05/29/2015	$      1,972.00
85576	Customer 4498	05/29/2015	$      1,971.00
89734	Customer 4609	03/02/2015	$      1,514.00
89733	Customer 4610	03/10/2015	$      1,513.00
89730	Customer 4611	02/27/2015	$      1,510.00
89724	Customer 4612	02/26/2015	$      1,504.00
89716	Customer 4613	02/24/2015	$      1,497.00
84415	Customer 4688	01/05/2015	$      1,198.00
84414	Customer 4689	01/05/2015	$      1,197.00
84391	Customer 4700	12/16/2014	$      1,124.00
84389	Customer 4701	12/15/2014	$      1,122.00
84387	Customer 4702	12/01/2014	$      1,082.00
84385	Customer 4703	11/19/2014	$      1,040.00
84383	Customer 4704	11/10/2014	$      1,003.00
84381	Customer 4705	11/07/2014	$          995.00
84379	Customer 4706	10/27/2014	$          957.00
84377	Customer 4707	10/07/2014	$          886.00
84375	Customer 4708	10/01/2014	$          857.00
84373	Customer 4709	09/22/2014	$          830.00
84371	Customer 4710	09/15/2014	$          803.00
84369	Customer 4711	08/29/2014	$          758.00
84367	Customer 4712	08/07/2014	$          683.00
84365	Customer 4713	08/05/2014	$          672.00
84363	Customer 4714	07/17/2014	$          627.00
84361	Customer 4715	07/09/2014	$          597.00
84359	Customer 4716	06/18/2014	$          483.00
84357	Customer 4717	06/05/2014	$          434.00
84355	Customer 4718	04/08/2014	$          340.00
84353	Customer 4719	04/08/2014	$          339.00
84293	Customer 4749	03/05/2014	$          230.00
84291	Customer 4750	03/04/2014	$          225.00
84289	Customer 4751	03/04/2014	$          224.00
84269	Customer 4761	02/15/2014	$          156.00
84259	Customer 4766	02/07/2014	$          138.00
84257	Customer 4767	02/05/2014	$          135.00
84255	Customer 4768	01/22/2014	$          127.00
84253	Customer 4769	01/29/2014	$          123.00

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  This will get your started.

Hope this helps.

[st-dat]

Hello,

Just to share an update for more help. 

I tried the queries below and they seems working, but need help to get other right such as: CurrMonth; Curr Fiscal Quarter; Prev Fiscal Quarter; Prev Fiscal Year (My Fiscal year starts in April 1st and ends in March 31)

CurWk:=CALCULATE([NetValue],ALL(dCalendar),FILTER(ALL(dCalendar),dCalendar[DayOfWeekNo]=MAX(dCalendar[DayOfWeekNo])-0),VALUES(dCalendar[DayOfWeek]))

PrevWk:=CALCULATE([Netvalue],ALL(dCalendar),FILTER(ALL(dCalendar),dCalendar[DayOfWeekNo]=MAX(dCalendar[DayOfWeekNo])-1),VALUES(dCalendar[DayOfWeek]))

PrevMonth:=CALCULATE([Netvalue],DATEADD(dCalendar[Date],-1,MONTH))

FiscalYr:=IF([Netvalue]>0,CALCULATE(me2nOffContract[%onContract],DATESYTD(dCalendar[Date],"03/31/2014")))

Thank you

[Ashish_Mathur]

Hi,

The measures will will depend upon what you select in the slicer - will you select a month, quarter, year?

[st-dat]

Hi Ashish,

Tested and works excellently. Thank so much.

My second related humble request, a need for measures to calculate Fiscal Year, PrevFiscalYear, Fiscal Quarter, PrevFiscalQuarter , MTD, PrevMTD. My Fiscal Year start: April 1st and End: March 30th.

I hope measures can be reuse by modifying to suit other purpose.

Thank you for your usual support.

st-dat

[st-dat]

Hello,

Thanks for your message, please expect a sample dataset in 15 mins. Regards

St-dat

[Anonymous]

DAX has a lot of inbuilt functions to handle some of your calculations. 

1/2)  Week Number: https://msdn.microsoft.com/en-us/query-bi/dax/weeknum-function-dax

DAX  makes working with weeks very complex so acheiving 1) and 2) will be tricky.This has already been solved here: https://community.powerbi.com/t5/Desktop/Calculating-Month-To-Date-and-Week-to-Date/m-p/67869#M28106

3) PreviousMonth: https://msdn.microsoft.com/en-us/query-bi/dax/previousmonth-function-dax
4) Month To Date: https://msdn.microsoft.com/en-us/query-bi/dax/totalmtd-function-dax

5) PreviousQuarter: https://msdn.microsoft.com/en-us/query-bi/dax/previousquarter-function-dax
6) Quarter To Date: https://msdn.microsoft.com/en-us/query-bi/dax/totalqtd-function-dax

7) PreviousYear: https://msdn.microsoft.com/en-us/query-bi/dax/previousyear-function-dax 
😎 Year To Date: https://msdn.microsoft.com/en-us/query-bi/dax/totalytd-function-dax

Hope this helps - reach out if you have any more questions or require further help!

[st-dat]

Thanks for your effort and reponse. Do or any forum members have any help on measures to sCalculate FISCAL PERIOD such as:

3) Last Fiscal Month

4) Current Fiscal Month To Date

5) Last Fiscal Quarter

6) Current Fiscal Quarter todate

7) Last Fiscal Year

😎 Current Fiscal Year To date

[Anonymous]

Do you have a column in your data set that idenitfiys the Fiscal Year? If you do you can do sometihng like this:

CurrentFiscalYear =

VAR

               CFiscalYear =

                              LOOKUPVALUE(

                                             'Date'[Fiscal Year]

                                             ,'Date'[Date]

                                             ,TODAY()

                              )

return 1 * ('Date'[Fiscal Year]= CFiscalYear)

For previous :

PreviousFiscalYear =

VAR

              PFiscalYear=

                              LOOKUPVALUE(

                                             'Date'[Fiscal Year]

                                             ,'Date'[Date]

                                             ,TODAY()

                              )

return 1 * (DATEADD('Date'[Fiscal Year],1,YEAR) = PFiscalYear)

[st-dat]

No thanks, I have no column as such that identify the fiscal year.

Thank you

[st-dat]

Thank you, I will work with your recommendations and provide feedback later.