Dax Calculation of January total

[Lucy64]

I need help with the calculation in Dax:

Below is the graph I need and the line is giving the correct results.  The number "850" is the total for January and I need to put in a formula to calculate month 1 instead. So the formula will work when I select a different year.  Thanks.

[v-micsh-msft · Accepted solution]

Hi Lucy64,

What is your current situation?

To use measure to help calculate the first month value of each year, we need add filter in the measure.

In addition to the month value, we also need to filter the year within Measure.

Follow the steps below:

1. If you don’t have a date table, create one, using the New table in Power BI desktop Data View, with the formula below:Then under relationship View, link this datetable with your data table, here we use sheet1 for example. 

   Datetable = CALENDAR(min(Sheet1[Date]),max(Sheet1[Date]))

2. In the newly created datetable, create a year column, with the formula below: 

   Year = year('Datetable'[Date])

3. Under the datetable, create a new measure with the formula below, we will use this measure to filter the years:

   yearC = IF( HASONEVALUE( 'Datetable'[Year] ), CALCULATE( VALUES( 'Datetable'[Year])))

4. Under your Data table, where you have [CalTotalIOT] calculated, create another measure to calculate the first month value, with the measure below:

   FirstmonthValue = calculate([CalTotalIOT], filter(ALLSELECTED(Sheet1),and(month(Sheet1[Date])=1, year(Sheet1[Date])=[yearC])))

5. Create the Precent measure with the measure below:

   Precent = ([Month1sales2]-[Measure])/[Month1sales2]

6. After that, when select the value in the Column and line chart, choose the date from the datetable, others from your data table. See my result:

Post back if any further assistance needed.

Regards

[Lucy64]

Michael, thank you very much.  I followed your instructions and it works perfect!  I really love power BI just wish I could get my head around the DAX language.  I need to look for an online course or some good books to read.

Thanks again.

[Greg_Deckler]

In general, something like:

MyMeasure = CALCULATE([CalTotalIOT],[Month] = 1)