How can we provide the year end string dynamically in TOTALYTD & DATESYTD?
Hi .., I am facing the issue of specifying the different yearends in TOTALYTD/DATESYTD functions. I require that according to the Site & Phase selected, the yearend be changed accordingly for the calculations. I have written the following dax which is throwing an error. Can anybody suggest a solution to this? C_RAG% YTD = var v_yearend = SELECTEDVALUE(Formulae[Contract_End]) Var Validselection = ISFILTERED('Dim_Sites'[Site & Phase]) && HASONEVALUE('Dim_Sites'[Site & Phase]) RETURN //TOTALYTD( [Actual C_RAG% Static] , ALL('Calendar'[Date]),ALL('Calendar'),v_yearend) IF(Validselection,CALCULATE([C_RAG%],ALL('Calendar'), DATESYTD('Calendar'[Date],v_yearend))) There are around 20 distinct values for Site & Phase and hence 20 different calendars followed. I do not want to hardcode it.
TOTALYTD variable year
Hi I need to pass thorugh a variable year end to TOTALYTD, however it's not working as hoped: Running Total Spend = VAR _EndDate = FORMAT(MAX('FY end'[Date]), "DD/MM") RETURN TOTALYTD(SUM('Invoiced Sales'[Spend]), 'Calendar'[Date], _EndDate) It seems as though the FORMAT function is changing the data type to text and therefore it isn't recognised by the TOTALYTD because it interperets the text as arguement 3 - ie the filter part. Is there a way to pass a variable date through to the year end arguement of the TOTALYTD formula? Many thanksTOTALYTD using variable year end date
Hi I am using this formula for running total: No issues with this but I am also using a parameter based on month int: I need the year end date to change in the formula. I have tried to use the power query editor to create a concatenated value: [Query=" declare @StartMonthInt int; set @StartMonthInt = '"&Start_Month_Int&"' declare @CurrentFY int; set @CurrentFY = ( select case when [MonthNum] < @StartMonthInt then datepart(yy,dateadd(year, -1, [DateFull])) else year([DateFull]) end from [CommRS_Core_Live].[dbo].[RS_Dates] where [DateFull] = cast(getdate() as date) ) declare @FYStartDate date; set @FYStartDate = ( select min([DateFull]) from [CommRS_Core_Live].[dbo].[RS_Dates] where datepart(yy,[DateFull]) = @CurrentFY and datepart(mm,[DateFull]) = @StartMonthInt ) declare @FYEndDate date; set @FYEndDate = (select dateadd(dd, -1, dateadd(year, 1, @FYStartDate))) --select '" & "' + cast(datepart(dd,@FYEndDate) as varchar(2)) + '/' + cast(datepart(mm,@FYEndDate) as varchar(2)) + '" & """' [FY end dd/mm] select @FYEndDate [FY end] "]), #"Duplicated Column" = Table.DuplicateColumn(Source, "FY end", "FY end - Copy"), #"Extracted Day" = Table.TransformColumns(#"Duplicated Column",{{"FY end - Copy", Date.Day, Int64.Type}}), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Day",{{"FY end - Copy", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"FY end - Copy", "FY end - Day"}}), #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "FY end", "FY end - Copy"), #"Extracted Month" = Table.TransformColumns(#"Duplicated Column1",{{"FY end - Copy", Date.Month, Int64.Type}}), #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Month",{{"FY end - Copy", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"FY end - Copy", "FY end - Month"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each Text.Combine({[#"FY end - Day"],[#"FY end - Month"]},"/")), #"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Custom", "FY end dd/mm"}}) in #"Renamed Columns2" I then passed this through to the formula like so: However it looks as though the formula is defaulting to 31/1 rather than 31/3 (in this instance where parameter value is 4, ie April): Do you have any idea how I can "trick" the TOTALYTD formula into accepting the concatenated value as a date value, or is there an alternative? Cheers
How to add previous month sales to current month sales
Hello all, I'm trying to compare the YTD sales with the goals on YTD format. This is the idea that I want to present: I can make a table adding the goal of each month, which then I can graph as the blue line. The table below: The problem I'm finding is how to add what was sold in Oct (in the image) with Nov, so those sales stack up in Nov, the same with Dec (which should be Oct, Nov and Dec stacked), and so on. The idea of the graph is to show how deviated the sales are resulting against the final goal amount. I tried with TOTALYTD, but it won't add up the months' results. Is there any other function? Or other visualization to achieve this? Thank you.
TotalYTD with start date and end date
Hello everyone, I am looking for a TotalYTD() function with a start date and an end date. I tried with a Flag_Start_Date with some If but I wasn't able to find the answer. The Start date should be always "01/09" And the End date should be the last date (DD/MM) of data. For example If I have some data from 01/01/2015 to 01/02/2017 (DD/MM/YYYY format) I want be build a table visualization like this : Year Qtr Sum Sales YTD Sales fiscal 2015 1 10 10 2015 2 10 2015 3 10 10 2015 4 10 20 2016 1 20 40 2016 2 20 2016 3 20 20 2016 4 20 40 2017 1 30 70 2017 2 30 2017 3 30 30 2017 4 30 60 I don't add Month column to not have to many rows in my table. Is it possible ? Thank you for your help, Regards, LoveboFollowing the ITALIANS, but wrong RUNNING TOTAL with TOTALYTD
I just followed a video from Alberto "Showing actuals and forecasts in the same chart with Power BI" (https://youtu.be/DKgF-5QHY68) as well their blog here -> https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/ I replicated successfully the example and wanted bring it a step further to my example. Unfortunately I got stuck computing the RUNNING TOTAL. First what I want to do: In month 5 (May) the last sales were made on e.g. May 15. In this case I want to show the Budget value instead of Sales. From there I want to show only the budget value from June to December. This also as a running total. I achieve that with RB2 Remaining Budget = VAR RB_lastsalesdate = CALCULATE(MAX(Orders1[OrderDate]), REMOVEFILTERS(Dates)) VAR RB_datecheck = IF(RB_lastsalesdate <= ENDOFMONTH(Dates[Date]), 1, 0) VAR RB_budgetafter = IF( RB_datecheck = 0, [Total Sales], [Total Budget adjusted] ) RETURN RB_budgetafter Success. Here we go! So far so good. This gives me a column that shows the TotalSales from Jan to April followed by the Budget from May to December. Now calculation the RUNNING TOTAL (RB2 cumulated) using TOTALYTD, as described in their blog. Unfortunatey I got a wrong result. The number 423,768 in 2008-5 is the sum of the budget from 1 - 5. From 2008-6 to 2008-12 it adds up the budget number as required, but on the wrong basis number from 2008-5; so actually the running total is wrongly calculated from June to December. The columns "Testing", "last sales date all" and "last date month" are just for understanding. I was not able to figure out, where the problem is neither to find a replacement for the TOTALYTD running total calculation. I guess, it can only achieved it with a "virtual table" holding the figures of the column "Remaining Budget" and then compute a running total. Ideas are hightly appreciated. Note: the sales granularity is on the dat while the Budget is monthly. <iframe width="600" height="373.5" src="https://app.powerbi.com/view?r=eyJrIjoiNzY2MDBlNGYtZWQzZC00MGM3LWI0ZWItMDk0NmMzZTFlZjgyIiwidCI6ImEwZTY5OTBhLTk5ZTctNDU2OC1hYWY4LWQxZDBkMTUwNzEzMiIsImMiOjl9" frameborder="0" allowFullScreen="true"></iframe>
Getting year-to-date for weekly data
I have been trying every version of year to date calculations I can find, but I keep just getting the current week in the field. Is the fact that it's weekly rather than monthly the issue? I'm a new Power Bi user, but I cannot find any tutorials anywhere to help.
