Using EOMONTH to find quarterly sum

Hi Anonymous ,

 

Believe that you can do two things:

1. Make a measure for quarter values (DAX)
2. Make a column with the daily figures (M Language)

 

Option 1.

• Create the following columns on your calendar table:

Quarter = "Q"&QUARTER([Date] -5)
End_of_month = EOMONTH([Date]-5;0)
//Forcing in both column to pick up th date from 5 days earlier as you have in the monthly calculation.

• Add the measure below for the Quartely calculation:

Quarter Reconciled Measure = 
SUMX (
    SUMMARIZE ( Dates; Dates[End_of_month]; "@Date_Selection"; MAX ( Dates[Date] ) );
    CALCULATE (
        SUM ( FLU_Snapshots[Actual_Value] );
        FILTER (
            ALL ( FLU_Snapshots[As of date] );
            FLU_Snapshots[As of date] = MAX ( [@Date_Selection] )
        )
    )
)

 

Option 2.

You also may make some query adaptations to get the daily figures:

• Add a step after the last step of your query
  • Rigth click the last step on the query settings and select Insert Step after
  • Rename this step for example to ====== (this will give you the notion of where you started to add new changes)
• Add a custom column with the following code:

PreviousDay =[As of date] - #duration(1,0,0,0)

• Remove the As of Date column
• Insert a Merge Querie
  • Do a Merger with the same querie
  • Select all columns that are unique identifiers for each row
  • On my example only have two (Description and As of Date)
  • Do not select the value or any other columns that change within the day otherwise you will get blank values
  • Select the left Join
• On the formula bar you will have:
  • Table.NestedJoin(#"Step Name", {"Description", "As of date"}, #"Step Name", {"Description", "PreviousDay"}, "Removed Columns", JoinKind.LeftOuter)
  • Replace the first Step Name (information highligted above) by the name of the step before the custom step ( ======)
• Expand the Actuals value from New column
• Add a new custom column:

[Actual_Value]-[Removed Columns.Actual_Value]

• Remove the column that you expanded before in my case [Removed Columns.Actual_Value]

Now you have daily values for all your KPI not sure if they are of any help but it's one way of getting daily values.

 

See attach PBIX file.