Forum Discussion

danb's avatar
danb
Icon for Resolver I rankResolver I
7 years ago
Solved

Combining Volumes into table with date ranges

Afternoon experts!    I am stumped. I have two tables, one that has pricing for different items by location and date range. The second table has the sales numbers by date / location. I would like t...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi danb ,

     

    You can add a measure or a column depending on your setup check formulas below:

     

    Measure =
CALCULATE (
    SUM ( Volume[Quantity] );
    FILTER (
        ALL ( Volume[Item]; Volume[Location]; Volume[Quantity]; Volume[Sales Date] );
        MAX ( Pricing[Item] ) = Volume[Item]
            && MAX ( Pricing[Location] ) = Volume[Location]
            && Volume[Sales Date] >= MAX ( Pricing[Price Start Date] )
            && Volume[Sales Date] <= MAX ( Pricing[Price End Date] )
    )
) + 0


Column =
CALCULATE (
    SUM ( Volume[Quantity] );
    FILTER (
        ALL ( Volume[Item]; Volume[Location]; Volume[Quantity]; Volume[Sales Date] );
        Pricing[Item] = Volume[Item]
            && Pricing[Location] = Volume[Location]
            && Volume[Sales Date] >= Pricing[Price Start Date]
            && Volume[Sales Date] <= Pricing[Price End Date]
    )
)

    Regards,

    MFelix

     

danb's avatar
danb
Icon for Resolver I rankResolver I
7 years ago

MFelix Thank you! That worked great. I am going to use the measure as it is a larger dataset. The only thing I had to adjust was replacing the semi-colons with commas. Not sure if I have an old version of Power BI or why I had to do that. 

 

Anyways, many thanks again!

 

Dan

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago
Hi danb,

The use of comma or dot comma is related with the regional settings not with the version of PBI, is the same as decimal mark that in some countries is the comma and on other the dot.


Regards,
MFelix