Forum Discussion

MRstwb's avatar
MRstwb
Regular Visitor
4 years ago
Solved

PowerBi Desktop YTD Filter

Hi everyone,

I am wondering if there is a way to output YTD values ​​as a sum of each month?

Via an interface to Salesforce I am able to import contracts based on a key date.
Logically, the contract budgets only appear once in the first month.

I would like to have an overview of the current (fixed) budgets (as of today) and sales opportunities, but as a YTD calculation. For example, if a budget of €20,000 is won in June, this is only included once in the YTD calculation.

Example:

January current budget: 10.000€
January sales opportunity: €5,000
February current budget: €20,000
February sales opportunity: 0€
March current budget: 10.000€
March sales opportunity: €5,000

Total current budget for the first quarter: €40,000.
Total sales opportunities for the first quarter: €10,000.

However, since the budget gained from January can be invoiced monthly, it must also be taken into account in February and March. --> According to PowerBi, the YTD total would be €50,000. In reality, however, this is €60,000 (since the sales opportunity from January in February and March must also be taken into account).

Is there a fix for this?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi MRstwb ,

     

    As far as I know, Dax doesn't support us to calculate the loop. Here I suggest you to try Power Query to calculate the loop.

    Here are some offical blogs and solved post, I hope they could help you solve your problem.

    For reference:

    Power Query - Loop calculating

    Power Query Loop Calculation

     

    Best Regards,
    Rico Zhou

     

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    MRstwb , YTD sales

     

    YTD =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = eomonth(_max,-1*MONTH(_max))+1
    return
    CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

     

    YTD budget

     

    YTD =
    var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _min = eomonth(_max,-1*MONTH(_max))+1
    return
    CALCULATE(calculate([budget], dateadd('Date'[Date],1,Month)) ,DATESBETWEEN('Date'[Date],_min,_max))

     

    assume net/sales and budget is measure

    • MRstwb's avatar
      MRstwb
      Regular Visitor
      4 years ago

      Hi amitchandak,

       

      thank's for your quick respone.

       

      But it seems that this gets me the same output and Powerbi does not aggregate the previous months.

       

      The YTD value shows me only 10.000€, but it should be in total 20.000€

      January: 5.000€

      February: 5.000€ from January

      March: 5.000€ from January plus 5.000€ from March.

       

       new acquired OpportunitiesOpportunities YTDPowerBi YTD
      January        5.000,00 €                 5.000,00 €     5.000,00 €
      February                     -   €                 5.000,00 €     5.000,00 €
      March        5.000,00 €               20.000,00 €   10.000,00 €

       

      Don't know if YTD is the right measure. I think it should be a calculating sum for each month which works as a loop.

       

      Thank you in advance.


      Best

      MRstwb

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi MRstwb ,

         

        As far as I know, Dax doesn't support us to calculate the loop. Here I suggest you to try Power Query to calculate the loop.

        Here are some offical blogs and solved post, I hope they could help you solve your problem.

        For reference:

        Power Query - Loop calculating

        Power Query Loop Calculation

         

        Best Regards,
        Rico Zhou

         

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