Forum Discussion

swathy429's avatar
swathy429
Regular Visitor
5 years ago

Help with DAX query

Hello Team,

 

I was trying to calculate a Card value using a DAX query. I have a set of data where the DAX formula should perform like

 

By taking the current Fiscal Quarter  we need to calculate a value using the below formula
Toal values = SUM(Prev  3 months Actuals value ) +( greater of Curent month ForeCast value OR actuals value) + Future ForeCast 
Actuals and forecasts are coming from a column called D.. Both the values are Number format 
 
For eg: The current month is October
total values = prev 3 months actual value should be the sum of (Jul +Aug+sept) +we need to compare forecast value & actuals values take the greater value for the current month + remaining month forecast value
 
 

sample data : 

DimensionYearMonthTotal
ActualsCFY20Jul4950
Forecast CFY20Aug867.5
ActualsCFY20Sep34149.5
ActualsCFY20Oct17542.2
ActualsCFY20Nov112.33
Forecast CFY21Dec83.17
ActualsCFY20Jan18127.8
ActualsCFY20Feb15424.5
ActualsCFY20Mar0
Forecast CFY20Apr-815.86
Forecast CFY21May0
Forecast CFY21Jun-210
ActualsCFY21Jul420
ActualsCFY21Aug39979
Forecast CFY21Sep10047.1
Forecast CFY21Oct19138.3
Forecast CFY21Nov-4672.5
Forecast CFY21Dec-5185
ActualsCFY20Jan-196
ActualsCFY20Feb0

 

 
General Comment
Need Help
Tips and Tricks

3 Replies

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

    swathy429 , if you have date, with date and time intelligence you can have

    Month vs month

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))

    Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Startofmonth(dateadd(Sales[Sales Date],-1,month)),3,MONTH))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • swathy429's avatar
      swathy429
      Regular Visitor
      5 years ago

      Thanks for the reply amitchandak  ,

      I dont have any dates in the data as you see in the sample data that's all column I Have

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello @swathy429 ,

    Since you do not have any date columns in the table, you will need to create the fym column as below screenshot shown.

    fym = FORMAT(DATEVALUE(20&RIGHT('Table'[Year],2)&"-"&'Table'[Month]),"YYYYMM")

    1.PNG

    Part One: SUM(Prev 3 months Actual value)

    Measure = CALCULATE(SUM('Table'[Total]),FILTER('Table','Table'[fym]>=FORMAT(EDATE(TODAY(),-3),"YYYYMM")&&'Table'[fym]<FORMAT(TODAY(),"YYYYMM")&&'Table'[Dimension]="Actuals"))

    Part Two: (largest foreCast value of the month Curent OR actual value)

    Measure 2 = 
var sum_total = CALCULATE(SUM('Table'[Total]),ALLEXCEPT('Table','Table'[Dimension],'Table'[fym]))
return
MAXX(ALLEXCEPT('Table','Table'[fym]),sum_total)

    And the third part of your formula is the part I don't quite understand. Can you share more information?

    The current result would be something like below.

    2.PNG

    Best regards

    Jay