Forum Discussion

fsy's avatar
fsy
Regular Visitor
9 years ago

Help with Timing Issue for Totals

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 A...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
fsy
Regular Visitor
8 years ago

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!

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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