Help with Timing Issue for Totals

[fsy]

I'm trying to create a comparison of my company's actual financials vs. budgeted vs. proforma forecast. All numbers are reported on a monthly basis and we follow an academic calendar that starts in August and ends in July. My data set comes from a datawarehouse where actual data is loaded monthly, but budgets and proformas are loaded in advance for the entire year. 

I'm having trouble creating comparisons where my budget and proforma totals reflect the same time period as the actuals since the budget and proforma run through the end of the year. For example, if I want to do a YTD and I only have actual financials through May 2017, I will get an actual total of Aug 2016-May 2017, but my budgets/proformas will create a total that is Aug 2016-July 2017 since we have values in budgets for entire year. I need help filtering the data that is totaled for my budgets and proformas so that they only reflect the months where we have actuals. 

My data set is organized as follows. The [Book] column is where I can filter between actual/budget/proforma. [Amount] containst the values that I'm trying to total.

I've created a separate calendar table using calendarauto that starts in August. I'm using this to create slicers by academic year. I have this linked to [TransactionDate] in my data table.

Thank you in advance for any help or insight!

FS

[v-shex-msft]

HI @fsy,

If your measure not contains any specific filters(all, allexcept) which will break the exit filters, you can try to add date filter to control this calculate range.

Actual YTD=calculate([YTD formula], FILTER(ALLSELECTED(Calendar),[Date]>=xxx&[Date]<=xxxx))

BTW, I can't find any different records from your screenshots.

Regards,

Xiaoxin Sheng

[fsy]

Thanks for your help Xiaoxin!

Is there a way to adjust the formula so that it detects the latest date with actual financials and then use that date to limit the budget and proforma data presented? I’m new to Power BI/DAX, so please correct me if I am wrong, but I think I would have to update the dates on a monthly basis going forward when new financials are loaded using the current formula.

Also, I tried your formula by doing the following:

Budget = CALCULATE(Sum('FinancialData'[Amount]),FinancialData[Book]="Standard Budget")

Budget YTD = calculate([Budget], FILTER(ALLSELECTED(DateTable),[DateKey]>=date(2011,1,1)&[DateKey]<=date(2017,6,1)))

How do I solve for this error.

"MdxScript(Model) (68, 86) Calculation error in measure 'FinancialData'[Budget YTD]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

Finally, I don't know why my first page of data is identical, but it does vary in the rows below. Not sure if this affects anything. Thanks again!

[v-shex-msft]

Hi @fsy,

Based on your error message, it seems like you are try to compare date value with text value. Did you ensure you input the right columns in your formula?

In addition, can you please share the sample file? I will test on it and try to modify your formula.

Regards,

Xiaoxin Sheng

[fsy]

Hi Xiaoxin,

I couldn't figure out out to upload my files onto this forum, so I've uploaded the to the Dropbox link here:

https://www.dropbox.com/sh/w46j50uwob4k7wi/AADNQqNkY8whh5paI7YGCX5Wa?dl=0

If there is another way I need to get files to you, please let me know. Really appreciate this!

[v-shex-msft]

Hi @fsy,

I find you have miss the '&' character.
Notice: '&' equal to CONCATENATE function, '&&' means and logic.

Budget YTD = calculate([Budget], FILTER(ALL(DateTable),[DateKey]>=date(2011,1,1)&&[DateKey]<=date(2017,6,1)))

 

Regards,

Xiaoxin Sheng