Displaying Monthly and Yearly values in matrix

[Anonymous]

Hi Guys

I am currently working on an experiement with Power BI, creating a Earned Value Dashboard. My first step was to have monthly information displayed only. 

Bit of background, so the project has estimation of figures up until completion, and have been asked for this to be displayed in a matrix, no problem. 

Currently the way I have it set is for it show both actuals and cumalative values, based on a filter from a slicer. The additional information is to show a yearly cumaltive figure, however, I am not having much luck being able to aggregate these values, I'm not sure if this has anything to to with the filter applied from the date table.

The tables being used have the following columns

ID      |    Date    |   Type  |  Value

I have attempted a number of different measures to help return the Year to date value, but it only seems to returning for the current month.

I've tried both TOTALYTD and DATESYTD but no sucess on either (I've found that I am unable to filter by type using the TOTALYTD.

Any help or suggestion would be highly appreciated.

[edhans]

In addition to @amitchandak 's comments, I strongly recommend you put the Date Table in Power Query then bring it into Power BI. You have much more granular control over your dates, and imported tables perform better than DAX created tables.

And to re-emphasize - to do what you are doing you must have a date table. Date Intelligence functions like TotalYTD require it.

See this post on how to create a date table in Power Query and make it fully dynamic so as data in your model changes, this table grows automatically at each refresh as needed.

[amitchandak]

They work best with date calendar. Also, datesytd or totalytd need an end date. If you do not have date selected then it will go till the calendar end date. I am giving a link of the file to check the same.

https://www.dropbox.com/s/6zc1c5di435thke/sales_analytics_shareV1.pbix?dl=0

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

To control the dates

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

Appreciate your Kudos.

[Anonymous]

Thanks for your reply. I do have a date table, and is related to all tables I am using. But the problem still persists. 

For example I used 

CALCULATE([Value_Last], 'LastTable'[Type]="PrevS", DATESYTD(DimDate[Date], "31/03"))

Value last is a measure that takes the Value for the last table, Then I filter for the type I want and the date to align with FY, But I am still only getting the result returned for the current month and not that of the year (for which I have) as the scope is already there. 

Do you have any other guidance on this?

Thanks in advance!

[edhans]

What do you get if you use this measure:

Measure =
TOTALYTD (
    [Value_Last],
    DimDate[Date],
    'LastTable'[Type] = "PrevS",
    "31/03"
)

And the "31/03" only works that way if you have your locale set properly to DD/MM/YYYY. Otherwise should be "03/31"

Also consider replacing [Value_Last] with the measure itself. You may be getting some context transition that the implicit CALCULATE() of using a measure reference that you aren't accounting for.

[Anonymous]

When I use your suggested measure it returns the result for the month the matrix is filtered for (using a slicer) and not an aggregate value for the year.

I had the idea that I could use a slicer to apply a filter to that data, and in following columns show information for the current FY.

[edhans]

You'll have to use ALL() around the dates. Your slicer is filtering out the dates the TOTALYTD function sees. It will not override the slicer. ALL() will.

Measure =
TOTALYTD (
    [Value_Last],
    ALL(DimDate[Date]),
    'LastTable'[Type] = "PrevS",
    "31/03"
)

[Anonymous]

Oddly, using the ALL() function removes any values from the column! 

From checking over of the formatting everything is as it was! 

Is there anything further I need to do? I did have a look to see if anyone else has come into this issue and couldnt see any

[edhans]

Without seeing your file or substantially more info, the only other suggestion I have for you is to make sure that you are only using the DimDates[Dates] field in measures and visuals. Even though it is related to OtherTables[Dates] fields, using those fields in measures and visuals can cause issues, and Time Intellience won't work right, or at all.

I always hide my other Dates fields so only the Dates[Date] field is visible when I am building reports.

[edhans]

without actually seeing your PBIX file and playing with it, it is difficult to determine what the issue is based on the description.