Latest Enterprise DNA Initiatives

How to get previous month in power BI

Power BI
#1

I have fact table which consists of Monthid Column as Integer which direct connect to date dimension
Productid MaterialId monthid Balance
1 1 1 100
1 1 2 200

Requiremnet is as below:

I have two slicers one is for current month and another one is for previous month
lets say currentmonth is feb2020 and previousmonth i selected as dec2019 i want to show diff of balances for feb2020 and dec2019 and thier cumulative totals

My question:

How to achive currentmonth and previous month when the date are integers in power bi and what is the soluion for the above scenario.kindly do needful

#2

Please see this thread, for a comparison using 2 slicers.


.
If you need any futher assistance.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is formatted to preformated text </>.
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Here are the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

#3

i have only monthid column in facttable whic is lined to date dimension.

from the facttable monthid column which is interger like 1(this is lets say jan 2010 in date dimension value)2(feb 2010) and so on …

now from the facttabke monthid column i want to compare two different time periods can be calculated lets say current period is jan 2020 and previous period is dec 2019 on user selection.

how to achive this

#4

It’s doesn’t seem like you have a date table at all. This is essential. And it’s covered in many areas within the portal

This is some fundamental Power BI concepts that you need to master before going any further.

Thanks
Sam

#6

It’s great to know that you are making progress with your query @amruthdna2018. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

#7

Thanks for your soluton. this is working fine but when i keeping both select month and preious month columns in the table its giving error saying “it doesnt have any relation”
how to achive this kidly do needful

#8

@amruthdna2018, please provide a sample/mock up file so members can better assist you in working towards a solution. Thanks

#9

Please find attached Spreadhsheet for Data and Sample .Pbix file

SamplePowerBidataset.pbix (67.8 KB)

Data.xlsx (11.5 KB)

I have written my requiremnet in output Tab.Kindly do needful

Thanks
Amrutha

#10

Thanks for that @amruthdna2018

So a few observations to start with:

  1. All relationships in your model were bi-directional. The ones outlined in red between your dimension- and facttable should actually be Single AND One-to-Many
    image
    image738×457 11.6 KB
  2. You’ve dragged and dropped a numeric field (Salesmount) in your table visual without creating a simple additive measure like: Sales = SUM( facttable[Salesmount] ) please make a habit of always creating Measures instead.
  3. The multiple rows were caused by dragging facttable[classtatus] in your table visual, once replaced by Measures that issue was sorted.

For any type of analysis it’s vital to have your Model set up correctly first so please take some time to go over this course and after that move on to Mastering DAX Calculations.

I hope this is helpful. Here’s your sample file.
SamplePowerBidataset.pbix (87.6 KB)

If this answers your question, please mark it as Solution. Thanks!

#12

This solution works to me…but when i place one slice like “Classificationstatus”, the table values are not getting filtering properly and values are not showing in table.Kindly do needful.

#13

Instead of a Slicer that will filter your fact table, see if you can use the visual level filter in the Filter Pane

#14

Customer needs classificationstatus as sliver based on the slicer selection current valure previous vaue currentclassification previousclassifcationvalues has to change

is there any soution for this…kindly do needful

#15

if any extra inputs needed i will upload my pbix file kinldy let me know