Calendar

[cristianml]

Hi,

I need a calendar that ends in August but monthly and not day by day. I tried to use the following measure

FY Date = CALENDARAUTO(8)

But I need this by Month instead of day.

The FY of my data need to be under a calendar that the Year ends on August so I can do a realtionship between data and FY Calendar in order to use YTD and MTD variables in dashboard.

Any ideas ?

Thanks

[MFelix]

Hi @cristianml ,

When making a calendar table it will give you all the days in a year, using the AUTOCALENDAR (8) your calendar is getting the fiscal year end in August so you can use it on your MTD and YTD that they will go from August to August.

You can the add on your calendar a Month column that will allow you to set your visuals in months description, although if you have time inteligence activated the table will automatically create a virtual hierarchy that allow to select YEAR, QUARTER, MONTH, DAY that you can use on your visuals.

If this is not the result you want can you specify want you mean by "need this by Month instead of day"? what is the purpose of this Month in your requirements.

Regards,

MFelix

[cristianml]

Hi,

The data is build under a monthly basis period and not allow me to create a relationship with a calendarauto with endyearmonth August cause this is by day. Also I realize that TOTALMTD and TOTALYTD requires an expression with "Dates" (by date) instead of month. What I need is in somehow show the MTD and YTD in same Dashboard as follow.

Thanks,

[Ashish_Mathur]

Hi,

In your Revenue and Forecast tables, there must be a Date field - if it not there, you will have to create one.  Even if you have a seperate field for Month and Year in those tables, a Date field can always be created.  You must then create a Calendar Table with running dates.  Thereafter build a relationship from the Date field of each Table to the Date field of the Calendar Table.  You will now be able to use all of the Date and Time Intelligence functions.

[cristianml]

Hi @Ashish_Mathur,

Not sure if I doing something wrong but I couldn't create a relationship between the calendar I created (calentarauto) with EndMonthDate August (8) and the queries. I created a relationship between the two calendars and do the YTD Measure buy only works from January instead of September that is the begining of the FY.

 

[Ashish_Mathur]

Hi,

I do not understand why there is a bi-directional relationship between the List Period and CalendarAuto Table.  There should be a relationship between the Date column of the FT Actuals Table to the Date column of the CalendarAuto Table.

[MFelix · Accepted solution]

Hi @cristianml ,

On your YTD measure you need to define the fiscal year ending.

Check this post  about issues with time inteligence on fiscal years.

Regards,

MFelix

[cristianml]

Thanks @MFelix, It worked ! 😄

[MFelix]

Hi @cristiami,

Create a new column on your period table with last day of the month for each date your formula should look like this:

Month end =
EOMONTH (
DATE ( YEAR ( LEFT ( Period[Period List], 4 ) ), MONTH ( RIGHT ( Period[Period List], 2 ) ), 1 ),
0
)

Then relate this column with the calendar table and MAKE your MTD and YTD calculation based on the calendar table.

Regards,
MFelix

[cristianml]

Hi @MFelix,

Sorry but I couldn't follow. This is what I meant...  I have my Period table by MONTH instead of by DAY. I imported this from Excel file just to filter by FY, Quarter and Period. The4th colum I made by changing the FORMAT to YYYY-MM.  This was the only way I could do at the begining to use filters but now i have trouble tring to do a YTD, MTD and ETC (Estimate to complete:).

Could you help me with the DAX Formula ? Thanks!