DAX Measures for Date while using Power BI Dataset

[Anonymous]

Hi, I am fairly new to Power BI.

I am using a dataset (Power BI dataset) as the source for my new report.  I am trying to create a new measure using data calculations.  Essentially, I have a column [Partner Deal Created Date] with date values: yyyy-mm-dd

I would like to add a measure column next to it with the previous month value (integer).  Example ideal output:

Partner Deal Created Date	previousMonth
2021-07-30	6
2021-06-30	5
2021-05-30	4

However whenever I try to perform a PREVIOUSMONTH (or just about any Date calculation for a measure), I get the following error when I add the measure to a table.

Formula:  

previousMonth = PREVIOUSMONTH('xxxxxxxxx xxxxxxxxxx'[Partner Deal Created Date])

Is this a limitation of using a Power BI dataset as the source or can this be achieved using a different formula?

My end goal is the be able to have a Measure as part of a slicer (which does not seem possible - I cannot add measures to slicers).

Thanks in advance!

[v-eqin-msft]

Hi @Anonymous ,

Could you please share me with your pbix file after removing sensitive data?

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

Based on my test

1. Connect to a Power BI dateset, and create [previousmonth] measure.

2. Create visuals on Power BI Desktop and publish the report to service. Both work fine on my side.

 Did you create visuals firstly in Desktop? Is your Power BI Desktop the latest version?

3. It's not supported to apply measures to slicer.

4. When connect to a Power BI dateset , the connection mode is Live connection, we could not create calculated columns in this mode.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-eqin-msft ,

Thanks for writing!  I succeeded to create the measure.  However, I still get the error when trying to add it to a new visual (table) or existing table (visual).  So unfortunately, I cannot even view the measured data (to confirm it is valid or not).

I created this in Power BI Desktop Version: 2.99.621.0 64-bit (November 2021) - I think the latest.

I am new to Power BI.  I am using Live Dataset.  What would the the negative impact if not Live Dataset?

Any thoughts on what this error means?

Thanks!

[amitchandak]

@Anonymous , if the date column is detected as date the 

new column

month(eomonth([Partner Deal Created Date],-1) )

[Anonymous]

@amitchandak

It looks like when I write this query, the column with dates cannot be selected. It seems only columns with number values are allowed. Not sure why. Thanks

[amitchandak]

@Anonymous , Hope you are creating a new column, not measure. A column should not stop the selection of a column

[Anonymous]

I am creating measures. The option to create new columns or add tables are not there. I think this is because I am using a power bi dataset as my source.