Forum Discussion
How to create a measure with a starting static value
I have a static actual value for April 2021 as 25.
I want to get the following months actual value using this static value.
Calculation is as below
For May-21, Actual value = Actual Value (April )+ Received Cost (May)-Cost of Sales (May )
50=25+35-10
Desired result is as below.
| Apr-21 | May-21 | Jun-21 | Jul-22 | |
| Received Cost | 40 | 35 | 30 | 50 |
| Cost of Sales | 15 | 10 | 12 | 15 |
| Actual Value | 25 | 50 | 68 | 103 |
How can I write to measure to calculate the following months actual value using just the April month value?
Cost of Sales and Received Cost are calculated using measures.
Sample pbix file link is provided in the link
https://drive.google.com/file/d/1Y7hoL91B6jsGIqZvqt9JHpcyb6Lpv5Ui/view
First make a DAX column with this expression
EOM = EOMONTH('Table'[Date], 0)And then use this measure to get your desired result.New =
VAR maxdate =
MAX ( 'Table'[EOM] )
VAR vSummary =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[EOM] ),
"cTS", [Cost of Sales],
"cRC", [Received Cost]
),
ALL ( 'Table' ),
'Table'[EOM] <= maxdate
&& 'Table'[EOM] >= DATE ( 2021, 4, 30 )
)
RETURN
SUMX ( vSummary, [cRC] - [cTS] )Pat
4 Replies
- VijayP
Community Champion
meghapillai
in powerQuery Editor , Unpivot The Data (only value columns)
in power bi report view
Use this function
VAR ActualValue = Calcualte(Sum(Value),filter(table, Column = "ACtual"))VAR CostValue = Calcualte(Sum(Value),filter(table, Column = "Cost of Sales"))
VAR ReceivedCost Value = Calcualte(Sum(Value),filter(table, Column = "Received Cost"))
Return
ActualValue+CostValue+ReceivedCostValueyou can drag that to Table or Matix to see the result
Thanks for the suggestion. But my data structure is a bit different. Received Cost and Cost of Sales values are result of 2 measures. I'm attaching a sample file here. Please see if you can help on generating the actual value measure, taking static value for april as 25.
Desired result is as below
Apr-21 May-21 Jun-21 Jul-21 Received Cost 40 35 30 50 Cost of Sales 15 10 12 15 Actual Value 25 50 68 103 https://drive.google.com/file/d/1Y7hoL91B6jsGIqZvqt9JHpcyb6Lpv5Ui/view?usp=sharing
- mahoneypat
Microsoft Employee
First make a DAX column with this expression
EOM = EOMONTH('Table'[Date], 0)And then use this measure to get your desired result.New =
VAR maxdate =
MAX ( 'Table'[EOM] )
VAR vSummary =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[EOM] ),
"cTS", [Cost of Sales],
"cRC", [Received Cost]
),
ALL ( 'Table' ),
'Table'[EOM] <= maxdate
&& 'Table'[EOM] >= DATE ( 2021, 4, 30 )
)
RETURN
SUMX ( vSummary, [cRC] - [cTS] )Pat
