Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Budgets and changed budgets (Forecasts)

Hey there, hope some of you can help me with this.

 

Right now we are having some changes to our budgets and forecasts, but the changes in the excel file only contains corrections from april to december 2020. 

 

This means, when we normally would have a line in our visual from january to december, we now only have a line from April to December. Is there anyone with a solution, to calculate the sum of the budget from April to December and then an IF statement, which then can take the previous month from the previous budget?

 

Right now the measure for the budget calculation looks simple as this:

 

Budget logistik = TOTALYTD(CALCULATE(SUM(factBudgetposter[Amount])),dimKalender[Date],dimKalender[Year]>2017)

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , I am hoping you have FY that start in April.

    Take calendar for here to get sorting right

    https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

    And is datesytd and totalyts use enddate as 3/31

     

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

     

     

    Power BI — YTD Questions — Time Intelligence 1–5
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    It is suggested to create a Dates table and create relationship between your fact table and the date table. Then, use the date column in Dates table as the x aixs.

     

    Reference: DAX 101: Creating a simple date table in DAX

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Right now the solution has not been provided, So I will try to explain it again, as the explanation might be the reason why.

       

      So today we have a budget excel file, which we are using in our power bi. 

      Then we have a forecast change, which only goes from april, this means that we want to show the original budget line, until the date where we have the forecast.

       

      Im trying to figure out how to "replace" the values in the budget from april and forwarad, whenever they are available.