YTD Revenue Calculation

[Atif]

I have tried the follwoing formula for my YTD Revenue calculation (Fiscal Year based), but I am not getting the running total.

YTD Revenue = TOTALYTD(SUM(Sheet1[Revenue]), 'Sheet1'[Date])

I have also applied the same formula with "date" column present in a separate file. Still the same result.

The function datesytd with last date being mentioned within quotes <"6/30"> is also not working.

[RolandsP]

Have you created Date table and established correct relationship with the Fact table

[Atif]

Yes, I have also tried it, but to no avail.

[Interkoubess]

Hi @Atif,

Could you please share dummy data or show how your data model.

It is not the 'Sheet1'[Date]) but rather the calendar[Date] in the formulae:

You can also try this following one:

YTD Revenue = calculate(SUM(Sheet1[Revenue]), dateytd('calendar'[Date],"6/30"))

Ninter

[Atif]

hi there Ninter @Interkoubess

The data is something like this in table 1

Station	Billing Unit	Revenue	Date	Publication	Primary
Karachi	DJK	13,500,000	7/1/17	Jang	72017
Karachi	DJK	13,500,000	8/1/17	Jang	82017
Karachi	DJK	13,500,000	9/1/17	Jang	92017
Karachi	DJK	13,500,000	10/1/17	Jang	102017
Karachi	DJK	13,500,000	11/1/17	Jang	112017
Karachi	DJK	13,500,000	12/1/17	Jang	122017
Karachi	DJK	0	1/1/18	Jang	12018
Karachi	DJK	0	2/1/18	Jang	22018
Karachi	DJK	0	3/1/18	Jang	32018
Karachi	DJK	0	4/1/18	Jang	42018
Karachi	DJK	0	5/1/18	Jang	52018
Karachi	DJK	0	6/1/18	Jang	62018
Karachi	TNK	9,000,000	7/1/17	News	72017
Karachi	TNK	9,000,000	8/1/17	News	82017
Karachi	TNK	9,000,000	9/1/17	News	92017
Karachi	TNK	9,000,000	10/1/17	News	102017
Karachi	TNK	9,000,000	11/1/17	News	112017
Karachi	TNK	9,000,000	12/1/17	News	122017
Karachi	TNK	0	1/1/18	News	12018
Karachi	TNK	0	2/1/18	News	22018
Karachi	TNK	0	3/1/18	News	32018
Karachi	TNK	0	4/1/18	News	42018
Karachi	TNK	0	5/1/18	News	52018
Karachi	TNK	0	6/1/18	News	62018

Table 2

Primary	Date
72011	7/1/11
82011	8/1/11
92011	9/1/11
102011	10/1/11
112011	11/1/11
122011	12/1/11
12012	1/1/12
22012	2/1/12
32012	3/1/12
42012	4/1/12
52012	5/1/12
62012	6/1/12
72012	7/1/12
82012	8/1/12
92012	9/1/12
102012	10/1/12
112012	11/1/12
122012	12/1/12
12013	1/1/13
22013	2/1/13
32013	3/1/13
42013	4/1/13
52013	5/1/13
62013	6/1/13

[RolandsP]

This does not seem to be correct Date table. Date table should be incremental and include all dates from minimum date up to maximum date in your fact table 

[Atif]

@RolandsP 

The "date" table have all the dates (uniques).

[Interkoubess]

Hi @Atif,

Did you try the formulae I gave?

Ninter

[Atif]

@InterkoubessYes, I have tried, but didn't work - Am I doing right by trying to create a relationship between the two tables using "Primary" column?

[Interkoubess]

Hi @Atif,

Nope. Please link the date from calendar with the date of your fact table.

Ninter

[Atif]

@InterkoubessWhile trying to connect the two tables using "date" column" it gives a message "You can't create a relationship between these two columns because one of the columns must have unique values."

Please note that "table 2" only contains unique values i.e., not being duplicated.

[Interkoubess]

Hi @Atif,

How did you create your calendar tabe?

Ninter

[Atif]

@Interkoubess

Yes, table 2

[Interkoubess]

Hi @Atif,

It seems not (to have unique values) otherwise the system won't show this message.

I tried a calendar with your dummy data and it worked on my side...

Please review yur calendar formula or table...

Ninter

[Atif]

@Interkoubess @RolandsP

I have used the option of "New Table" under "Modeling" tab.

The formula I used is "Calendar = CALENDAR(MIN(Sheet1[Date]),MAX(Sheet1[Date]))".

[Atif]

@Interkoubess @RolandsP

The "date" column" is used to relate the two tables with "cardinality" being "many to one" and "cross filter direction" as "single".

When I use "YTD Revenue = CALCULATE(SUM(Sheet1[Revenue]), 'Calendar'[Date])" it displays "0" in the first column and the rest of the years have values of each mont rather than having a running total.

When I use "YTD Revenue = TOTALYTD(SUM(Sheet1[Revenue]), 'Calendar'[Date])" it only displays "0".

[Interkoubess]

Hi @Atif,

With your formula with calculate your missed DATEYTD

Please add it like this:

YTD Revenue = CALCULATE(SUM(Sheet1[Revenue]),DATEYTD( 'Calendar'[Date],"6-30"))

OR ( date format with /)

YTD Revenue = CALCULATE(SUM(Sheet1[Revenue]),DATEYTD( 'Calendar'[Date],"6/30"))

Ninter

[Atif]

@Interkoubess

The formula "YTD Revenue = CALCULATE(SUM(Sheet1[Revenue]), DATESYTD('Calendar'[Date], "6/30"))" is displaying "0" in "column 1" and respective values of "FY18" in "column 2".

The figures for previous years are now missing.

The running total is also not being calculated.

[Interkoubess]

Hi @Atif,

Why are you talking about column1.

It is a measure not a calculated column....

It worked on my side so you are doing something wrong...

Ninter

[Atif]

@Interkoubess

Do you think I have correctly created "calendar" table by using "Calendar = CALENDAR(MIN(Sheet1[Date]),MAX(Sheet1[Date]))"?

I have checked the said table in "data mode" and no date is missing.

Do you think I have correctly linked the two tables by using column "date"?

If above two steps are accurate, then kindly share the formula for "YTD Revenue".

[Atif]

@Interkoubess @RolandsP

The formula "YTD Revenue = TOTALYTD(SUM(Sheet1[Revenue]), 'Calendar'[Date])" returns a table with column headings "Month Name" and "Total" and row heading "Total".

The formula "YTD Revenue = CALCULATE(SUM(Sheet1[Revenue]), 'Calendar'[Date])" returns all values, but not the running total. For e.g. 100 for July and 100 for August. I need 100 for July and the value to be added subsequently in following months.