Compare 2 Dates by year but without Hierarchy

[Joseph_Hchaime]

I have a bills table and a "payments" table. 

Both have Bill ID as common and a "Bill Issue Date" in the bills table and an "Allocation Date" in the Payments table. 

I'm trying to create a dax measure that will dynamically show me the Net Billed and Net Collected based on Year. Where the Net Collected is split between Current Year and Previous Years. 

Example 1: 

We are in 2022.

Bill issued in 2019 for 100,000$. 

First Collection of $50,000 was made in 2020

Second Collection of $50,000 was made in 2021. 

I pick 2021 as the Allocation Date/Year from the slicer. It should show me the following 

Total Collected $100,000. CY $50,000 PY $50,000

I'm facing the issue of creating this DAX dynamically, I'm only able to do it if i use the today function but that hard codes it to today's "year" which is 2022 so anything before "today" is considered previous years irrespective of the slicer selection. 

Also, there is no date hierarchy in my data set so I'm forced to create "bins" from the issue_date and allocation_Date

Please help!! 

[daXtreme · Accepted solution]

Hi @Joseph_Hchaime 

Why don't you have a date dimension in your model? And if you don't, why can't you create one? Without such a dimension your model will be flawed because you won't have a shared dimension that will be able to join to your date fields in both tables.

Can you please show us the model? Just paste a screenshot if you can. Thanks.

[v-jianboli-msft · Accepted solution]

Hi @Joseph_Hchaime ,

Based on your description, I have created a simple sample:

Please try:

First, create a new table for slicer:

Turn on the single select of the slicer:

Then create the measures:

Total Collected = CALCULATE(SUM(Payment[Ammount]),FILTER('Payment',YEAR([Allocation Date])<=SELECTEDVALUE('For slicer'[Year])))

CY = CALCULATE(SUM(Payment[Ammount]),FILTER('Payment',YEAR([Allocation Date])=SELECTEDVALUE('For slicer'[Year])))

PY = CALCULATE(SUM(Payment[Ammount]),FILTER('Payment',YEAR([Allocation Date])=SELECTEDVALUE('For slicer'[Year])-1))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft · Accepted solution]

Hi @Joseph_Hchaime ,

Based on your description, I have created a simple sample:

Please try:

First, create a new table for slicer:

Turn on the single select of the slicer:

Then create the measures:

Total Collected = CALCULATE(SUM(Payment[Ammount]),FILTER('Payment',YEAR([Allocation Date])<=SELECTEDVALUE('For slicer'[Year])))

CY = CALCULATE(SUM(Payment[Ammount]),FILTER('Payment',YEAR([Allocation Date])=SELECTEDVALUE('For slicer'[Year])))

PY = CALCULATE(SUM(Payment[Ammount]),FILTER('Payment',YEAR([Allocation Date])=SELECTEDVALUE('For slicer'[Year])-1))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme · Accepted solution]

Hi @Joseph_Hchaime 

Why don't you have a date dimension in your model? And if you don't, why can't you create one? Without such a dimension your model will be flawed because you won't have a shared dimension that will be able to join to your date fields in both tables.

Can you please show us the model? Just paste a screenshot if you can. Thanks.

[Joseph_Hchaime]

I'm direct querying data from an SQL server so when I publish on the service it gives me the latest results. 

I'm still new to PowerBI. I'm under the impression I can't alter the schema I use on my local PC as it won't publish properly and sync the data from the live SQL server. 

Are you saying I can create my own date table and link both the bills and allocations table to it without affecting the schema i'm direct querying from SQL? if so how do i do it? I have active relationships between the Bills Table and Allocations table based on Bill_ID 

[daXtreme · Accepted solution]

Create a composite model and you'll be able to mix remote and local sources. That's easy enough. Here's the documentation: Use composite models in Power BI Desktop - Power BI | Microsoft Docs

You can also find vids on YT about composite models. Some of them will be from Marco Russo and Alberto Ferrari. But if you're new to PBI... I'd suggest you first take some speedy course on the basics of PBI. Without a sound knowledge of the basics you'll be having a hard time creating good and correct models. Been there, seen that.

But up to you 🙂