Forum Discussion

01_RAF_01's avatar
01_RAF_01
Frequent Visitor
5 years ago

Help needed with some DAX calculations

Hi im new here. Starting to work more and more with DAX calculations in PowerBi. Learning every day and could use some help. I have been working on setting up a forecast step by step, these are the first steps. 

The data i have is as follows: 
1 dimension date table

1 facttable where amount of received requests are stored  (i made a relationship from date received to datetable)

 

I have been able to get these measures: 

- total current requests in stock (until last date) :

VAR Dat = Max(Dates[Date])
RETURN
Calculate(COUNT(
'Ss'[CN]),
'Ss'[CreateDate]<Dat,
(Not('Ss'[CloseDate]<(Dat)) || ISBLANK('Ss'[CloseDate])),
ALL(Dates[Date]))



- received request per date:

Instr= CALCULATE(COUNTROWS('Ss'))


- 30days MA:

CALCULATE(

AVERAGEX(
(DATESINPERIOD(Dates[Date],
Max(Dates[Date]),
-30,DAY)), CALCULATE([Instr],Dates[Workday]=1)))  < This filters the weekends out of the data

Would appreciate some help on these topics:

1) 30 days MA...  I need the average to be counted until today. Now i also see data for the future 30 days. Best would be to see the 30day MA untill the last day (today).

2) I want to sum up (cummulate) the 30days MA untill the end of my datetable (end datetable is today()+30) . So starting today.

3) So basically with these 2 calculations i would have a projection for the next 30days. Current resuest in stock + cummulative MA would project the stock for the coming 30 days. 
 
This is my first challenge which i have to work on, hope someone has some great ideas/ solutions. 
DAX Calculation
DAX Filtering
DAX Measures

2 Replies

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

    01_RAF_01 , Not very clear typically

    Rolling 30 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,Day))

     

    Rolling 30 day from today= CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],today(),-30,Day))

     

    next 30

     

    Rolling 30 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),30,Day))

     

    Rolling 30 day from today= CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],today(),30,Day))

     

     

    last to next 30

     

     

    Rolling 30 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date])-30,60,Day))

     

    Rolling 30 day from today= CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],today()-30,60,Day))

     

    But if you want to travel workday

     

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

  • 01_RAF_01's avatar
    01_RAF_01
    Frequent Visitor
    5 years ago

    Thanks for the reply amitchandak .. To make it a bit clear ill try to explain it step by step. See screenshot below. 
    19 april for example was the last date there are requests received. Untill that day the amount of open requests (not finished) was 4640. 

    I made a 60day MA with this dax measure: 

    CALCULATE(
    AVERAGEX(
    (DATESINPERIOD(Dates[Date],
    Max(Dates[Date]),
    -60,DAY)), CALCULATE([requestsreceivedtoday],Dates[Workday]=1)))


    as you can see the MA also goes in the future, i would like to see that it calculated the MA until the last day that there are requests received and after that date it shows that MA.
    So in this example until april 19th it should show 130, and for all future dates also 130 instead of changing...

     

    i cant do this by the way: 

    CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-30,Day))

     

    to calculate the amount received requests i have a measure of countrows... i cant do a sum of a measure right?