Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sridharbabu
Helper I
Helper I

Loading 2 months of data, has to show latest month record

Loading two months of data into power bi table, If item,location,type is same for current month and previous month sum of them and show in the table. If item,location is same and type is different then sum of current month and previous month, but has to show current month record.. as shown in the example, not showing month name in the table. just for understanding i kept in table. in table aslo having other columns, measures, where m1,m2,m3 measures shows values of current, last, last-1 month data. also has to show top 100 based on measure m5.

Input         
monthitemlocationtypeqtycol1m1m2m3m5
apr3453abcpen34     
mar3453abcpen54     
apr1232abcpencil23     
mar1232abcpen66     

 below is the output how  i need.

output         
monthitemlocationtypeqtycol1m1m2m3m5
apr3453abcpen88     
apr1232abcpencil69     

 in output table also not showing month column. taking it backend.

below is the dax i am using where its showing correct output when item,location,type all are same for current and last month. but not able to get current month if, type , col1 is different. we are using monthno to get current , last month data.

Dax query i used is

last2month=var thismonth=max(table[monthno])

return

calculate(sum(table[qty]),

allexcept(table,table[item],table[location]),

table[monthno]>=thismonth-1 && table[montho]<=thismonth

)

 

Always used to load 2 months of data everymonth. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@sridharbabu , I usually prefer to create a month from date and the use a date table to do time intelligence

 

example measures

 

Using selected date of Today

 

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Or refer : Power BI Time Intelligence- Show month names instead of Current Month Vs Last Month

https://youtu.be/x0DvPn1QeO4

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@sridharbabu , I usually prefer to create a month from date and the use a date table to do time intelligence

 

example measures

 

Using selected date of Today

 

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Or refer : Power BI Time Intelligence- Show month names instead of Current Month Vs Last Month

https://youtu.be/x0DvPn1QeO4

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.