Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Power BI DAX command for QTD Line chart issue

Hi,

 

I would like to get recommendation for the QTD line chart showing Bump after each month ans starting of new month, expected outcome is to see growing as we are calculating cumulative numbers. Here are the logics we used.

1. 

Cumulative Actuals plus Cumulative MQL Projection =
If(Calculate(Sum('Date'[Date]))>=TODAY(),
([Cumulative Actuals] + [Cumulative MQL Projection]),"")
 
2.
Cumulative MQL Projection =
if(
max('date'[date]) < today(),
0,
if(
max('date'[date]) >= today() && max('date'[date]) <= eomonth(today(),0),
calculate(SUM('Global MQL Projection'[MQL Projection - Remaining Daily Average])) * (datediff(today(),MIN('date'[date]),day)+1),
calculate(sum('Global MQL Projection'[MQL Projection - Remaining Daily Average])) * calculate(sum('Date'[Day of Quarter]))
)
 
)
 
3. 
MQL Projection - Remaining Daily Average =
if(eomonth('Global MQL Projection'[Old Style Month],0) < today(),
0,
'Global MQL Projection'[MQL Projection] //need a way to bring in actuals and subtract MTD actuals from Projection
/
calculate(count('Date'[Date]),filter('date','Date'[Old Style Month]='Global MQL Projection'[Old Style Month] && 'Date'[Date] >= today())))
 

Please advice how to modify the logic to create a stagnent growing Line for projection and Forecast where there is a drop down after every month.

 

Thanks

Tips and Tricks

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    datediff won't do you any good - months have different lengths.

     

    Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

    Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

      Is it possible to provide the PBIX file attached for the dummy data. The major conflict is happening in this code, when i removed +1 that resolved the issue for the current month drop down but still happening with the May to June. Instead I am expecting cumulative growing numbers so there should not be any drop down. What other function can be used except DatedIFF.

      Appreciate your assistance

      Cumulative MQL Projection = 
if(
    max('date'[date]) < today(),
        0,
        if(
            max('date'[date]) >= today() && max('date'[date]) <= eomonth(today(),0),
                calculate(SUM('Global MQL Projection'[MQL Projection - Remaining Daily Average])) * (datediff(today(),min('date'[date]),day)),
                calculate(sum('Global MQL Projection'[MQL Projection - Remaining Daily Average])) * calculate(sum('Date'[Day of Quarter]))
        )
)

       

       

       
      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        let go of the month concept and instead work with the individual dates in the quarter.

         

        Use one of the internet file services to post your sample data.