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

help on sameperiodelast year

Hi All,

 

I've one Dax which gives me no of volume and have derived with help of Dax. Now problem is, I've 2024 year-month column, i also derived last year 2023 year-month column. infact I've volume for current year and 2023 as well by creating 2 dax.. but dont know how to get that values against every month.. for e.g. below is the scenario.

Now I dont understand how I can get volume 2023 values next to 150, 250 & 350 i.e. 100,200,300 and then I want new colume after volume 2023 "Difference" so minus of 150-100 value need in that column..

 

current yearprevious yearvolume 2024volume 2023

 

Jan-2023-100
 Feb-2023-200
 Mar-2023-300
Jan-2024Jan-2023150-
Feb-2024Feb-2023250-
Mar-2024Mar-2023350-

 

Can any one please guide here..

10 REPLIES 10
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1713683051168.png

 

 

Jihwan_Kim_0-1713683011858.png

Volume measure: = 
SUM( volume_fact[volume] )

 

Previous year volume measure: = 
CALCULATE (
    SUM ( volume_fact[volume] ),
    SAMEPERIODLASTYEAR ( calendar_dimension[Date] )
)

 

Difference measure: = 
VAR _current = [Volume measure:]
VAR _previous = [Previous year volume measure:]
VAR _condition =
    _current <> BLANK ()
        && _previous <> BLANK ()
        && HASONEVALUE ( calendar_dimension[Month-Year] )
RETURN
    IF ( _condition, _current - _previous )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Hi Jihwan_Kim, I used now date table field date column and I got the values, but as per SAMEPeriodelastyear dax, against Jan 2024 its not showing me Jan 2023 value.

belwo is the snip

PowerBIBeginer_0-1713711172741.png

 

Hi,

Please share your sample pbix file's link, and then I can try to look into it.

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


this is the main table from where i pulll volume (i.e.output column)- 

in example I purposefully change names.. I cant share pbi file since in transform data more tables are connected to main table (main table my naming conv is tblname)

PowerBIBeginer_0-1713777792776.png

date table

PowerBIBeginer_4-1713778320285.png

 

 

model view

PowerBIBeginer_2-1713778007179.png

and here's the dax

PowerBIBeginer_3-1713778223400.png

 

 

Hi,

In the Calendar Table, create calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year and Month name from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

In the Calendar Table, create calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year and Month name from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Jihwan_Kim.. Appriciate your effort for understanding mu issue exactly well and try to plot across very nicely. I would like to hightlight here something that, Volume Measure you've taken simply sum of one column, even I do that but using one filter condition in that.

for e.g.

volume = calculate(sum(tblname[volume]), tblname[onecolumn]="VC")

and hence I designed Previous year Volume like below but I got blanks..

Previuos year volume = CALCULATE(
                        SUM(tblname[Volume]),
                        tblname[onecolumn]="VC",
                        SAMEPERIODLASTYEAR(tblname[date]))

 

Hi,

You have not created a calendar Table, as suggested by Jihwan.  Study his solution carefully.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello Ashish Sir, please refer post no 6 in which I post that I've now used date table and date field even and I got the values but against 2024 2023 value is not appearing

Thank you so much for your reply and valuable time share for me. Let me try this and will update you on the same.. 

Helpful resources

Announcements
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.

Top Solution Authors