Can I add column to a dimension in powerbi that is conneted to an existing read-only semantic model?

[wazza77]

Hi

I have a semantic read-only model that we use in our company. The model has a [first day of month] column in the deimension I am using.

What I wanted to do is create a new column [month behind] for a dimenension I am using in powerbi thats 1 month behind the [first day of month] column, so that it would look like this :

[first day of month]    [month behind]

01/04/2024                 01/03/2024

01/05/2024                 01/04/2024

01/06/2024                 01/05/2024

etc                               etc 

I wanted to use this so I could display two graphs - one for the current month and one for the previous month

Not sure if this is possible in powerbi or not or would we need to alter the whole model? Or could we somehow do this via DAX?

Thanks in advance.

[bchager · Accepted solution]

@wazza77  Try using a calculated column for your previous month instead, using the expression I provided.

[v-csrikanth]

Hi @wazza77 
Yes @bchager, Its meets the requirement which @wazza77  is looking for in the above responses in this case.

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @wazza77 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @wazza77 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @bchager , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

Best regards,
Cheri Srikanth

[bchager · Accepted solution]

@v-csrikanth Maybe we can mark this one as resolved?

[Ashish_Excel]

Hi,

You may create this calculated column formula

Previous month = edate(Data[Date],-1)

However, you actually do not even need this column.  Within a measure, itself, you may use the PREVIOUSMONTH() function in the second argument of the CALCULATE() function.

[wokka]

HI

So I added a local model to my pbix file , created a new measure called [month behind].

[month behind] = ( PREVIOUSMONTH ( 'Calendar_table'[First day of month] ) )

I have a slicer on the desktop that chooses the [First day of the month] from the same Calendar dimension , so when I choose say 01/05/2024 I get a [month behind] value of 01/04/2024 , which is correct.  The idea behind the slicer is when I choose say 01/05/2024, one graoph which uses that date just displays data for that month, but the graph for the previous month should then show data for  01/04/2024 

I also went to Format, Edit Interractions clicked on the month slicer and selected the [previous month] table so the slicer couldnt affect the previous month table.

However, whgen I drag the [month behind] into the table, I see the correct month, but I also get what seems like a year worth of data, not limited to just that month.

Not sure what is going on now, can someone please suggest what might be happening please? 

[Ashish_Excel]

Hi,

Not sure what you want but try this measure

PY = calculate(sum(Data[sales]),previousmonth(calendar[date]))

[wokka]

HI

So I'm still new to powerbi so its a steep learning curve.

I have an exsiting dimension calendar table, and one of the columns in the dimension is [first of month]

What I do is create a table visual, drag in a couple of other columns like [Sales] and it displays the sales for the month we want. In this case the [first of month] has value of 01/05/2024, so we see all sales for the month of May 2024.

What I wanted to do was have a [first of month] slicer that allows me to choose the first of month, I have set up 2 tables on the desktop - one displays the data from first of month of 01/05/2024 and the other for the preceeding/previous month of 01/04/2024. 

The problem I have is trying to get the table that displays the previous month data that has first of month 01/04/2024. 

[Ashish_Excel]

Drag the measure i suggested to the second table.

[wazza77]

Apologies, my knowledge of powerbi is limited.

So Ive stripped out everything from the table to make it as simple as possible.

This is what I have :

[Sales]                 - a Measure that covers all sales for all months and years

[First of month]  - a Dimension column ( e.g.  01/01/2024, 01/02/2024, 01/03/2024 etc )

So the table looks this in its simplest form :

[Sales]    

400005

Then if i drag in the [First of month] column, it changes to this :

[Sales]   [First of month]

1003      01/01/2024

2934      01/02/2024

6537      01/03/2024

etc

Then if I create a measure for previous month :

[month behind] = ( PREVIOUSMONTH ( 'Calendar_table'[First day of month] ) )

and drag it into the table I get this :

[Sales]    [month behind]   

400005   

Hopefully this info is useful. I think maybe I havent explained things clearly?

[bchager · Accepted solution]

@wazza77  Try using a calculated column for your previous month instead, using the expression I provided.

[wazza77]

Apologies, my knowledge of powerbi is limited.

So Ive stripped out everything from the table to make it as simple as possible.

This is what I have :

[Sales]                 - a Measure that covers all sales for all months and years

[First of month]  - a Dimension column ( e.g.  01/01/2024, 01/02/2024, 01/03/2024 etc )

So the table looks this in its simplest form :

[Sales]    

400005

Then if i drag in the [First of month] column, it changes to this :

[Sales]   [First of month]

1003      01/01/2024

2934      01/02/2024

6537      01/03/2024

etc

Then if I create a measure for previous month :

[month behind] = ( PREVIOUSMONTH ( 'Calendar_table'[First day of month] ) )

and drag it into the table I get this :

[Sales]    [month behind]   

400005   

Hopefully this info is useful. I think maybe I havent explained things clearly?

[Ashish_Excel]

Share the download link of the PBI file.

[wokka]

Hi

Apologies, unfortunately company policy doesnt allow me to share the pbix file.

[bchager]

@wazza77  You'll need to create a local model by clicking on Transform Data within the Home tab. Just close the query editor after that and you should be able to create a calculated column.

[wokka]

Thank you for the suggestion. I did create a local model.

When you say "create a calculated column" did you mean creating a new column on the existing Dimension table, by doing right click on the dimension table, choose "New Column" , then set it as  :

[new column] = ( PREVIOUSMONTH ( 'Calendar_table'[First day of month] ) )

So I am hoping that this would create a new column in the dimension table so it would look something like this :

[First day of month]       [new column]

01/01/2024                    01/12/2023

01/02/2024                    01/01/2024

01/03/2024                    01/02/2024

etc 

Then I am hoping I could drag [First day of month] & [new column]  into the table on desktop ?

[bchager]

@wokka @wazza77  (are you using 2 user accounts here?) Yes to both. You could use this DAX expression:

Column = EDATE('Table'[First Day of Month], -1)