Running Total Help

Hi All

Sorry for the delay in getting back - I took a few days off for the holidays.

A bit of background.

I thought I'd start my PBI journey with by creating a report that displays the current monthly production values against budget and to also show the month by month production values for the current fiscal year again against budgets.  I thought that this would be simple (based on me being able to create this in SQL in a few minutes).

Since my last reply I started over and I have now got a version that is working but I'd like you all to have a look and critique. 

The data model has only 2 tables: a date table, Dates, (marked as such) and a daily production table, DailyProd.

As this report will only show monthly/fiscal yearly data based on the last production date there are no filters/slicers on the page.

All the ** bleep** in the code is 3 letter abbreviation for Cumulative.

The first measure I created is:

Daily Prod Value = sum(DailyProd[day_sales])

then a cumulative daily production value measure:

**bleep** Daily Prod Value = 
    calculate(sum(DailyProd[day_sales]),
                Dates[CurDate]<=max(Dates[CurDate]))

Next I created a measure to filter only month in which latest production value is:

CM Daily Prod Value = 
    var maxpdate = MAXX(allselected(DailyProd),DailyProd[pdate])
    var CM_EOM =eomonth(maxpdate,0)
    var FirstDay = date(year(maxpdate),MONTH(maxpdate),1)
    RETURN
    CALCULATE([Daily Prod Value],
    filter(all(DailyProd[pdate]),
            DailyProd[pdate] >= FirstDay && DailyProd[pdate]<= CM_EOM)
    )

I then ended up creating 2 measures to create a running monthly total by day:

**bleep** CM Daily Prod Value = 
        CALCULATE([CM Daily Prod Value],
                  Dates[CurDate]<=max(Dates[CurDate])
                   
        )

The issue I had here was that the measure calculated a running total up to the maxium day in the dates table not just to the end of current production month.  I tried to add various filters etc to the measure but failed. So I then created one additional measure to restrict the output to only 1 month:

**bleep** CM Daily Prod Value CM = 
var maxpdate = MAXX(allselected(DailyProd),DailyProd[pdate])
    var CM_EOM =eomonth(maxpdate,0)
    var FirstDay = date(year(maxpdate),MONTH(maxpdate),1)
    RETURN
calculate([**bleep** CM Daily Prod Value],filter(Dates,Dates[CurDate]>=FirstDay && Dates[CurDate]<=CM_EOM))

Is it possible to do this filtering in 1st measure?

Fiscal Year 

I created the following measure:

CY Daily Prod Value = 
        CALCULATE([Daily Prod Value],
                    FILTER(all(Dates[FiscalYear]),
                            Dates[FiscalYear]=calculate(max(Dates[FiscalYear]),Dates[CurDate]=maxx(allselected(DailyProd[pdate]),DailyProd[pdate]))
            )
        )

Using this method with the Month Year field from the dates gives me the monthly total from the start of the fiscal year, July.

I again had to create 2 measures for the same reason above (to limit output for only July-December):

CY YTD Prod Value = CALCULATE([CY Daily Prod Value],DATESYTD(Dates[CurDate],"30/06"))

and prevent the above measure outputing to the last month in the Dates table I created this measure:

**bleep** CY YTD Prod Value Max PDate = 
var maxmonth = eomonth(maxx(ALLSELECTED(DailyProd[pdate]),DailyProd[pdate]),0)
RETURN
CALCULATE([CY YTD Prod Value],filter(Dates,Dates[CurDate]<=maxmonth))

Again my question is, is it not possible to do this filtering in the 1st measure?

So I have a working report now but I believe that there must be a more eloquent way of achieving this.

Again thanks everyone for your help.

Jake